package com.yulian.web_api.report;

import java.math.BigDecimal;
import java.util.*;

import com.healthmarketscience.sqlbuilder.CustomSql;
import com.healthmarketscience.sqlbuilder.InCondition;
import com.healthmarketscience.sqlbuilder.SelectQuery;
import com.yulian.repository.CommonRepositoryImpl;
import com.yulian.util.CommonUtils;

import io.netty.util.internal.logging.InternalLogger;
import io.netty.util.internal.logging.Log4JLoggerFactory;
import io.vertx.core.CompositeFuture;
import io.vertx.core.Future;
import io.vertx.core.Promise;
import io.vertx.core.json.JsonArray;
import io.vertx.core.json.JsonObject;
import io.vertx.pgclient.PgPool;
import io.vertx.sqlclient.Tuple;

/**
 * 该类位于业务逻辑层，主要实现供应商批次商品成本斤价管理，为MainVerticle中的REST接口提供服务
 *
 * @author
 */
public class SupplierBatchGoodsCostPriceManager {
    final InternalLogger logger = Log4JLoggerFactory.getInstance(SupplierBatchGoodsCostPriceManager.class);
    CommonRepositoryImpl commonRepository = new CommonRepositoryImpl();

    /**
     * 根据传入的查询参数获取满足条件的记录
     *
     * @param client
     * @param config
     * @param bodyJSObj
     * @return
     */
    public Future<JsonObject> getRecords(PgPool client, JsonObject config, JsonObject bodyJSObj) {
        Promise<JsonObject> promise = Promise.promise();
        //最后返回的JsonObject对象
        JsonObject responseJSObj = new JsonObject();
        // 验证必须传递的字段
        List<String> must_fields = Arrays.asList("big_area_id_list", "site_id_list", "warehouse_id_list", "class_lv1_id_list", "class_lv2_id_list", "variety_id_list", "aggregation_id_list");
        JsonObject must = this.commonRepository.verifyAllParamMust(bodyJSObj, must_fields);
        if (must != null) {
            promise.complete(must);
            return promise.future();
        }
        // 验证必须传的数值字段
        List<String> numeric_fields = Arrays.asList("page_size", "page_index");
        JsonObject numeric = this.commonRepository.verifyAllParamNumeric(bodyJSObj, numeric_fields);
        if (numeric != null) {
            promise.complete(numeric);
            return promise.future();
        }

        //从bodyJson中获取数据   各字段注释请参看数据库设计文档中数据表supplier_batch_goods_cost_price的说明
        String big_area_id_list = bodyJSObj.getString("big_area_id_list");
        String site_id_list = bodyJSObj.getString("site_id_list");
        String warehouse_id_list = bodyJSObj.getString("warehouse_id_list");
        String class_lv1_id_list = bodyJSObj.getString("class_lv1_id_list");
        String class_lv2_id_list = bodyJSObj.getString("class_lv2_id_list");
        String variety_id_list = bodyJSObj.getString("variety_id_list");
        String aggregation_id_list = bodyJSObj.getString("aggregation_id_list");


        int page_size = Integer.valueOf(bodyJSObj.getString("page_size"));
        int page_index = Integer.valueOf(bodyJSObj.getString("page_index"));
        int query_offset = (page_index - 1) * page_size;
        int query_limit = page_size;
        //用于传递给构建sql的JsonObject
        JsonObject queryJSObj = new JsonObject();
        queryJSObj.put("big_area_id", big_area_id_list);
        queryJSObj.put("site_id", site_id_list);
        queryJSObj.put("warehouse_id", warehouse_id_list);
        queryJSObj.put("class_lv1_id", class_lv1_id_list);
        queryJSObj.put("class_lv2_id", class_lv2_id_list);
        queryJSObj.put("variety_id", variety_id_list);
        queryJSObj.put("aggregation_id", aggregation_id_list);
            this.commonRepository.getCon(client).onSuccess(conn -> {
                //用于传递给构建sql的JsonObject
                String sql = createSelectSql(queryJSObj, query_offset, query_limit);
                //获取满足条件的记录总数
                String count_sql = this.commonRepository.createCountSql(queryJSObj, "supplier_batch_goods_cost_price");
                Future<Integer> getTotalFuture = this.commonRepository.getRecordsCount(conn, count_sql, "getRecordsSalesComplaintsSubsidyDetailTotal");
                Future<JsonArray> future =  this.commonRepository.queryJsonArray(conn, sql, "getRecordsSalesComplaintsSubsidyDetailTotal");
                CompositeFuture.join(Arrays.asList(getTotalFuture, future )).onComplete(ar2 -> {
                    if (ar2.succeeded()) {
                        int total_records = Integer.valueOf(ar2.result().list().get(0).toString());
                        JsonObject dataJSObj = new JsonObject();
                        JsonArray recordsJSArray=(JsonArray)ar2.result().list().get(1);
                        for (int i = 0 ; i < recordsJSArray.size();i++){
                            JsonObject JsonObject = recordsJSArray.getJsonObject(i);
                            //期末库存总金额
                            double end_inventory_amount = Double.parseDouble(JsonObject.getString("end_inventory_amount")==null? "0":JsonObject.getString("end_inventory_amount"));
                            BigDecimal bigDecimal1= new BigDecimal(end_inventory_amount);
                            //商品折损供应商补贴金额
                            double good_damage_supplier_subsidy_price = Double.parseDouble(JsonObject.getString("good_damage_supplier_subsidy_price")==null? "0":JsonObject.getString("good_damage_supplier_subsidy_price"));
                            BigDecimal bigDecimal2= new BigDecimal(good_damage_supplier_subsidy_price);
                            //问题商品供应商赔偿金额
                            double unqualify_compensation_affirm_amount= Double.parseDouble(JsonObject.getString("unqualify_compensation_affirm_amount")==null? "0":JsonObject.getString("unqualify_compensation_affirm_amount"));
                            double unqualify_compensation_affirm_amount2= Double.parseDouble(JsonObject.getString("unqualify_compensation_affirm_amount2")==null? "0":JsonObject.getString("unqualify_compensation_affirm_amount2"));
                            BigDecimal bigDecimal3= new BigDecimal(unqualify_compensation_affirm_amount);
                            BigDecimal bigDecimal4= new BigDecimal(unqualify_compensation_affirm_amount2);
                            double unqualify_compensation_affirm_amount_all=bigDecimal3.add(bigDecimal4).doubleValue();
                            JsonObject.put("unqualify_compensation_affirm_amount_all",unqualify_compensation_affirm_amount_all);
                            //商品内部赔偿金额
                            double indoor_subsidy_price = Double.parseDouble(JsonObject.getString("indoor_subsidy_price")==null? "0":JsonObject.getString("indoor_subsidy_price"));
                            BigDecimal bigDecimal5= new BigDecimal(indoor_subsidy_price);
                            //期末库存总重量
                            double end_inventory_weight = Double.parseDouble(JsonObject.getString("end_inventory_weight")==null? "0":JsonObject.getString("end_inventory_weight"));
                            BigDecimal bigDecimal6= new BigDecimal(end_inventory_weight);
                           //供应商批次商品成本斤价
                            BigDecimal bigDecimal7 =bigDecimal1.subtract(bigDecimal2).subtract(bigDecimal3).subtract(bigDecimal4).subtract(bigDecimal5);
                            BigDecimal bigDecimal0 = new BigDecimal(0.00);
                            if (bigDecimal7.compareTo(bigDecimal0)>0 && bigDecimal6.compareTo(bigDecimal0)>0){
                                double cost_per_kilogram =bigDecimal7.divide(bigDecimal6).doubleValue();
                                JsonObject.put("cost_per_kilogram",cost_per_kilogram);
                            }
                            JsonObject.put("cost_per_kilogram",0.00);
//                            String goods_batch_code =JsonObject.getString("goods_batch_code");
//                            if (goods_batch_code.contains("s")){
//                                String str10 = goods_batch_code.substring(1,goods_batch_code.length()-1);
//                                JsonObject.put("goods_batch_code",str10);
//                            }
                        }
                        dataJSObj.put("total", total_records).put("records",recordsJSArray);
                        responseJSObj.put("code", 200).put("msg", "获取记录成功").put("data", dataJSObj);
                        promise.complete(responseJSObj);
                    } else {
                        logger.info("获取记录数据发生错误: {}", ar2.cause().getMessage());
                        promise.complete(CommonUtils.createExceptionJsonObject(ar2, "getRecordsSalesComplaintsSubsidyDetailTotal", "获取供应商批次商品成本斤数据失败"));
                    }
                });
           }).onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getRecordsSalesComplaintsSubsidyDetailTotal", "数据库连接失败")));
        return promise.future();
    }

    /**
     * 创建查询符合搜索条件记录的sql语句
     *
     * @param queryJS      body参数对象
     * @param query_offset 页面偏移
     * @param query_limit  页面记录数
     * @return 查询符合搜索条件的sql语句
     */
    public String createSelectSql(JsonObject queryJS, int query_offset, int query_limit) {
        SelectQuery sq = new SelectQuery();
        sq.addCustomColumns(new CustomSql("a.id"),   new CustomSql("a.supply_chain_goods_weight_package_spec"),       new CustomSql("(select h.big_area_name   from big_area h where h.is_delete=0  and h.id=a.big_area_id ) as big_area_name"),
                        new CustomSql("(select h.site_name  from site h   where h.is_delete=0   and h.id=a.site_id ) as site_name"),
                        new CustomSql("warehouse_id"),new CustomSql("(select w.warehouse_name " +
                                "from warehouse w " +
                                "where w.is_delete=0 " +
                                "and w.id=a.warehouse_id limit 1 ) as warehouse_name "),
                        new CustomSql("aggregation_id"),
                        new CustomSql("(select ag.aggregation_name " +
                                "from  aggregation_goods ag " +
                                "where ag.is_delete=0 " +
                                "and id=a.aggregation_id limit 1 ) as aggregation_name"),
                        new CustomSql("( select  q.class_name   from goods_class_lv1 q  where q.is_delete=0 and q.id =a.class_lv1_id limit 1 ) as class1_name"),
                        new CustomSql("( select  q.class_name   from goods_class_lv2 q  where q.is_delete=0 and q.id =a.class_lv2_id limit 1 ) as class2_name"),
                        new CustomSql("( select  q.class_name  from goods_variety q  where q.is_delete=0  and q.id =a.variety_id  limit 1 ) as variety_name "),new CustomSql("a.time_line"),
                        new CustomSql("(select sum(ibd.inventory_number) as inventory_number from initial_batch_detail ibd where ibd.is_delete=0 and ibd.supply_chain_goods_id in (select gp.supply_chain_goods_id from goods_parameter gp where gp.is_delete=0 and gp.aggregation_id=a.aggregation_id) and ibd.warehouse_id=a.warehouse_id " +
                                " and ibd.supply_chain_goods_settle_package_spec=a.supply_chain_goods_settle_package_spec and ibd.time_line=a.time_line and ibd.goods_batch_code=a.goods_batch_code )as inventory_number "),
                        new CustomSql("(select sum(ibd.inventory_weight) as inventory_weight from initial_batch_detail ibd where ibd.is_delete=0 and ibd.supply_chain_goods_id in (select gp.supply_chain_goods_id from goods_parameter gp where gp.is_delete=0 and gp.aggregation_id=a.aggregation_id) and ibd.warehouse_id=a.warehouse_id " +
                                " and ibd.supply_chain_goods_settle_package_spec=a.supply_chain_goods_settle_package_spec and ibd.time_line=a.time_line and ibd.goods_batch_code=a.goods_batch_code )as inventory_weight "),
                        new CustomSql("(select sum(ibd.inventory_weight*ibd.land_price) as inventory_land_acount from initial_batch_detail ibd where ibd.is_delete=0 and ibd.supply_chain_goods_id in (select gp.supply_chain_goods_id from goods_parameter gp where gp.is_delete=0 and gp.aggregation_id=a.aggregation_id) and ibd.warehouse_id=a.warehouse_id " +
                                " and ibd.supply_chain_goods_settle_package_spec=a.supply_chain_goods_settle_package_spec and ibd.time_line=a.time_line and ibd.goods_batch_code=a.goods_batch_code )as inventory_land_acount "),
                        new CustomSql("(select sum(ibd.end_inventory_number) as end_inventory_number from initial_batch_detail ibd where ibd.is_delete=0 and ibd.supply_chain_goods_id in (select gp.supply_chain_goods_id from goods_parameter gp where gp.is_delete=0 and gp.aggregation_id=a.aggregation_id) and ibd.warehouse_id=a.warehouse_id " +
                                " and ibd.supply_chain_goods_settle_package_spec=a.supply_chain_goods_settle_package_spec and ibd.time_line=a.time_line and ibd.goods_batch_code=a.goods_batch_code )as end_inventory_number "),
                        new CustomSql("(select sum(ibd.end_inventory_weight) as end_inventory_weight from initial_batch_detail ibd where ibd.is_delete=0 and ibd.supply_chain_goods_id in (select gp.supply_chain_goods_id from goods_parameter gp where gp.is_delete=0 and gp.aggregation_id=a.aggregation_id) and ibd.warehouse_id=a.warehouse_id " +
                                " and ibd.supply_chain_goods_settle_package_spec=a.supply_chain_goods_settle_package_spec and ibd.time_line=a.time_line and ibd.goods_batch_code=a.goods_batch_code )as end_inventory_weight "),
                        new CustomSql("(select sum(ibd.end_inventory_weight*ibd.land_price) as end_inventory_amount from initial_batch_detail ibd where ibd.is_delete=0 and ibd.supply_chain_goods_id in (select gp.supply_chain_goods_id from goods_parameter gp where gp.is_delete=0 and gp.aggregation_id=a.aggregation_id) and ibd.warehouse_id=a.warehouse_id " +
                                " and ibd.supply_chain_goods_settle_package_spec=a.supply_chain_goods_settle_package_spec and ibd.time_line=a.time_line and ibd.goods_batch_code=a.goods_batch_code )as end_inventory_amount "),
                        new CustomSql("(select sum(ibd.accept_number) as accept_number from initial_batch_detail ibd where ibd.is_delete=0 and ibd.supply_chain_goods_id in (select gp.supply_chain_goods_id from goods_parameter gp where gp.is_delete=0 and gp.aggregation_id=a.aggregation_id) and ibd.warehouse_id=a.warehouse_id " +
                                " and ibd.supply_chain_goods_settle_package_spec=a.supply_chain_goods_settle_package_spec and ibd.time_line=a.time_line and ibd.goods_batch_code=a.goods_batch_code )as accept_number "),
                        new CustomSql("(select sum(ibd.accept_weight) as accept_weight from initial_batch_detail ibd where ibd.is_delete=0 and ibd.supply_chain_goods_id in (select gp.supply_chain_goods_id from goods_parameter gp where gp.is_delete=0 and gp.aggregation_id=a.aggregation_id) and ibd.warehouse_id=a.warehouse_id " +
                                " and ibd.supply_chain_goods_settle_package_spec=a.supply_chain_goods_settle_package_spec and ibd.time_line=a.time_line and ibd.goods_batch_code=a.goods_batch_code )as accept_weight "),
                        new CustomSql("(select sspgia.supplier_pickup_goods_inspect_accept_code " +
                                "from sales_supplier_pickup_goods_inspect_accept sspgia " +
                                "where sspgia.is_delete=0 " +
                                "and sspgia.id=(select sspgiad.sales_supplier_pickup_goods_inspect_accept_id   from sales_supplier_pickup_goods_inspect_accept_detail  sspgiad where sspgiad.is_delete=0 " +
                                " and sspgiad.id =( select ibd.sales_supplier_pickup_goods_inspect_accept_id from initial_batch_detail ibd where ibd.is_delete=0 and ibd.supply_chain_goods_id in " +
                                "   (select gp.supply_chain_goods_id from goods_parameter gp where gp.is_delete=0 and gp.aggregation_id=a.aggregation_id) " +
                                "    and ibd.warehouse_id=a.warehouse_id and ibd.supply_chain_goods_settle_package_spec=a.supply_chain_goods_settle_package_spec and ibd.time_line=a.time_line and  ibd.goods_batch_code=a.goods_batch_code " +
                                " )) ) as supplier_pickup_goods_inspect_accept_code"),
                        new CustomSql("(select sspgia.supplier_pickup_goods_inspect_accept_code  from supplier_pickup_goods_inspect_accept  sspgia  where sspgia.is_delete=0  and sspgia.id=(select sspgiad.supplier_pickup_goods_inspect_accept_id  from supplier_pickup_goods_inspect_accept_detail  sspgiad " +
                                "  where sspgiad.is_delete=0  and sspgiad.id =(  select ibd.supplier_pickup_goods_inspect_accept_id from initial_batch_detail ibd where ibd.is_delete=0  and ibd.supply_chain_goods_id in " +
                                "   (select gp.supply_chain_goods_id  from goods_parameter gp   where gp.is_delete=0  and gp.aggregation_id=a.aggregation_id)  and ibd.warehouse_id=a.warehouse_id " +
                                "   and ibd.supply_chain_goods_settle_package_spec=a.supply_chain_goods_settle_package_spec and ibd.time_line=a.time_line and  ibd.goods_batch_code=a.goods_batch_code " +
                                "    ))) supplier_pickup_goods_inspect_accept_code2"),
                        //仓库，结算包规、时间轴、批次号一样 不同供应链商品名 的到岸斤价一样
                        new CustomSql("(select ibd.land_price from initial_batch_detail ibd where ibd.is_delete=0 and ibd.supply_chain_goods_id in (select gp.supply_chain_goods_id from goods_parameter gp where gp.is_delete=0 and gp.aggregation_id=a.aggregation_id) and ibd.warehouse_id=a.warehouse_id " +
                                " and ibd.supply_chain_goods_settle_package_spec=a.supply_chain_goods_settle_package_spec and ibd.time_line=a.time_line and ibd.goods_batch_code=a.goods_batch_code limit 1 )as land_price "),
                        new CustomSql("(select sum(ibd.accept_weight*ibd.land_price) as accept_amount from initial_batch_detail ibd where ibd.is_delete=0 and ibd.supply_chain_goods_id in (select gp.supply_chain_goods_id from goods_parameter gp where gp.is_delete=0 and gp.aggregation_id=a.aggregation_id) and ibd.warehouse_id=a.warehouse_id " +
                                " and ibd.supply_chain_goods_settle_package_spec=a.supply_chain_goods_settle_package_spec and ibd.time_line=a.time_line and ibd.goods_batch_code=a.goods_batch_code )as accept_amount "),
                        new CustomSql("(select ibd.goods_batch_code from initial_batch_detail ibd where ibd.is_delete=0 and ibd.supply_chain_goods_id in (select gp.supply_chain_goods_id from goods_parameter gp where gp.is_delete=0 and gp.aggregation_id=a.aggregation_id) and ibd.warehouse_id=a.warehouse_id " +
                                " and ibd.supply_chain_goods_settle_package_spec=a.supply_chain_goods_settle_package_spec and ibd.time_line=a.time_line and ibd.goods_batch_code=a.goods_batch_code  limit 1 )as goods_batch_code "),
                        new CustomSql("(select sum(srwd.retrun_warehouse_weight) from sales_return_warehouse_detail srwd where srwd.is_delete=0 and srwd.goods_batch_code=a.goods_batch_code  and srwd.warehouse_id=a.warehouse_id and srwd.delivery_date=a.time_line and srwd.supply_chain_goods_settle_package_spec=a.supply_chain_goods_settle_package_spec and srwd.delivery_date=a.time_line and srwd.supply_chain_goods_id in ( select gp.supply_chain_goods_id from goods_parameter gp  where gp.is_delete=0 and gp.aggregation_id=a.aggregation_id )) as retrun_warehouse_weight"),
                        new CustomSql("(select sum(srwd.retrun_warehouse_number) from sales_return_warehouse_detail srwd where srwd.is_delete=0 and srwd.goods_batch_code=a.goods_batch_code  and srwd.warehouse_id=a.warehouse_id and srwd.delivery_date=a.time_line and srwd.supply_chain_goods_settle_package_spec=a.supply_chain_goods_settle_package_spec and srwd.delivery_date=a.time_line and srwd.supply_chain_goods_id in ( select gp.supply_chain_goods_id from goods_parameter gp  where gp.is_delete=0 and gp.aggregation_id=a.aggregation_id )) as retrun_warehouse_number"),
                        new CustomSql("(select sum(ibd.loss_number) as loss_number from initial_batch_detail ibd where ibd.is_delete=0 and ibd.goods_batch_code=a.goods_batch_code and ibd.supply_chain_goods_id in (select gp.supply_chain_goods_id from goods_parameter gp where gp.is_delete=0 and gp.aggregation_id=a.aggregation_id) and ibd.warehouse_id=a.warehouse_id " +
                                " and ibd.supply_chain_goods_settle_package_spec=a.supply_chain_goods_settle_package_spec and ibd.time_line=a.time_line )as loss_number "),
                        new CustomSql("(select sum(ibd.loss_weight) as loss_weight from initial_batch_detail ibd where ibd.is_delete=0 and ibd.goods_batch_code=a.goods_batch_code and ibd.supply_chain_goods_id in (select gp.supply_chain_goods_id from goods_parameter gp where gp.is_delete=0 and gp.aggregation_id=a.aggregation_id) and ibd.warehouse_id=a.warehouse_id " +
                                " and ibd.supply_chain_goods_settle_package_spec=a.supply_chain_goods_settle_package_spec and ibd.time_line=a.time_line )as loss_weight "),
                        new CustomSql("(select sum(ibd.profit_loss_number) as profit_loss_number from initial_batch_detail ibd where ibd.is_delete=0 and  ibd.goods_batch_code=a.goods_batch_code and ibd.supply_chain_goods_id in (select gp.supply_chain_goods_id from goods_parameter gp where gp.is_delete=0 and gp.aggregation_id=a.aggregation_id) and ibd.warehouse_id=a.warehouse_id " +
                                " and ibd.supply_chain_goods_settle_package_spec=a.supply_chain_goods_settle_package_spec and ibd.time_line=a.time_line )as profit_loss_number "),
                        new CustomSql("(select sum(ibd.profit_loss_weight) as profit_loss_weight from initial_batch_detail ibd where ibd.is_delete=0 and ibd.goods_batch_code=a.goods_batch_code and ibd.supply_chain_goods_id in (select gp.supply_chain_goods_id from goods_parameter gp where gp.is_delete=0 and gp.aggregation_id=a.aggregation_id) and ibd.warehouse_id=a.warehouse_id " +
                                " and ibd.supply_chain_goods_settle_package_spec=a.supply_chain_goods_settle_package_spec and ibd.time_line=a.time_line )as profit_loss_weight "),
                        new CustomSql("(select sum(sdpcd.shift_delivery_plan_completion_number) from shift_delivery_plan_completion_detail sdpcd where sdpcd.is_delete=0 and sdpcd.goods_batch_code=a.goods_batch_code and sdpcd.warehouse_id=a.warehouse_id and sdpcd.supply_chain_goods_settle_package_spec=a.supply_chain_goods_settle_package_spec and sdpcd.delivery_date=a.time_line and sdpcd.supply_chain_goods_id in " +
                                "( select gp.supply_chain_goods_id from goods_parameter gp where gp.is_delete=0 and gp.aggregation_id=a.aggregation_id ) ) as shift_delivery_plan_completion_number"),
                        new CustomSql("(select sum(sdpcd.shift_delivery_plan_completion_weight) from shift_delivery_plan_completion_detail sdpcd where sdpcd.is_delete=0 and sdpcd.goods_batch_code=a.goods_batch_code and sdpcd.warehouse_id=a.warehouse_id and sdpcd.supply_chain_goods_settle_package_spec=a.supply_chain_goods_settle_package_spec and sdpcd.delivery_date=a.time_line and sdpcd.supply_chain_goods_id in " +
                                "( select gp.supply_chain_goods_id from goods_parameter gp where gp.is_delete=0 and gp.aggregation_id=a.aggregation_id ) ) as shift_delivery_plan_completion_weight"),
                        new CustomSql("(select sum(prd.purchase_return_weight) from purchase_return_detail prd where prd.is_delete=0 and prd.warehouse_id=a.warehouse_id and prd.purchase_return_date=a.time_line and prd.goods_batch_code=a.goods_batch_code  " +
                                "  and prd.supply_chain_goods_id in ( select gp.supply_chain_goods_id from goods_parameter gp where gp.is_delete=0 and gp.aggregation_id=a.aggregation_id )) as purchase_return_weight"),
                        new CustomSql("(select sum(prd.purchase_return_number) from purchase_return_detail prd where prd.is_delete=0 and prd.warehouse_id=a.warehouse_id and prd.purchase_return_date=a.time_line  and prd.goods_batch_code=a.goods_batch_code  " +
                                "  and prd.supply_chain_goods_id in ( select gp.supply_chain_goods_id from goods_parameter gp where gp.is_delete=0 and gp.aggregation_id=a.aggregation_id )) as purchase_return_number"),
        new CustomSql("(select sum(scsd.sales_complaints_subsidy_price) as sales_complaints_subsidy_price " +
                "from sales_complaints_subsidy_detail scsd " +
                "where scsd.is_delete=0 " +
                "and scsd.warehouse_id=a.warehouse_id " +
                "and scsd.goods_batch_code=a.goods_batch_code " +
                "and scsd.trade_date=a.time_line " +
                "and scsd.supply_chain_goods_settle_package_spec=a.supply_chain_goods_settle_package_spec " +
                "  and scsd.supply_chain_goods_id in " +
                "      (select gp.supply_chain_goods_id " +
                "       from goods_parameter gp " +
                "       where gp.is_delete=0 " +
                "         and gp.aggregation_id=a.aggregation_id) ) as sales_complaints_subsidy_price"),
        new CustomSql("(select sum(sc.unqualify_compensation_affirm_amount) as unqualify_compensation_affirm_amount   from supplier_compensation sc  where sc.is_delete=0   and sc.warehouse_id=a.warehouse_id  and sc.supplier_pickup_goods_inspect_accept_detail_id=(   select ibd.sales_supplier_pickup_goods_inspect_accept_id from initial_batch_detail ibd where ibd.is_delete=0 " +
                "    and ibd.goods_batch_code=a.goods_batch_code  and ibd.warehouse_id=a.warehouse_id  and ibd.supply_chain_goods_settle_package_spec=a.supply_chain_goods_settle_package_spec and ibd.time_line=a.time_line  )   and sc.generate_date=a.time_line " +
                "  and sc.aggregation_id=a.aggregation_id ) as unqualify_compensation_affirm_amount"),
        new CustomSql("(select sum(sc.unqualify_compensation_affirm_amount) as unqualify_compensation_affirm_amount  from sales_supplier_compensation sc  where sc.is_delete=0   and sc.warehouse_id=a.warehouse_id " +
                "  and sc.sales_supplier_pickup_goods_inspect_accept_detail_id=(  select ibd.sales_supplier_pickup_goods_inspect_accept_id from initial_batch_detail ibd where ibd.is_delete=0  and ibd.goods_batch_code=a.goods_batch_code " +
                "  and ibd.warehouse_id=a.warehouse_id  and ibd.supply_chain_goods_settle_package_spec=a.supply_chain_goods_settle_package_spec and ibd.time_line=a.time_line  )  and sc.generate_date=a.time_line  and sc.aggregation_id=a.aggregation_id ) as unqualify_compensation_affirm_amount2"),
        new CustomSql("supply_chain_goods_settle_package_spec"), new CustomSql("goods_batch_code"),
        new CustomSql("(select gdssd.good_damage_supplier_subsidy_code from good_damage_supplier_subsidy_detail gdssd where gdssd.is_delete=0 and gdssd.goods_batch_code=a.goods_batch_code " +
                "and gdssd.generate_date=a.time_line and gdssd.warehouse_id=a.warehouse_id and gdssd.real_supply_chain_goods_settle_package_spec=a.supply_chain_goods_settle_package_spec  ) as good_damage_supplier_subsidy_code"),
        new CustomSql("(select gdssd.good_damage_supplier_subsidy_price from good_damage_supplier_subsidy_detail gdssd where gdssd.is_delete=0 and gdssd.goods_batch_code=a.goods_batch_code " +
                "and gdssd.generate_date=a.time_line and gdssd.warehouse_id=a.warehouse_id and gdssd.real_supply_chain_goods_settle_package_spec=a.supply_chain_goods_settle_package_spec  ) as good_damage_supplier_subsidy_price"),
        new CustomSql("(select gdssd.indoor_subsidy_price from good_indoor_subsidy_detail gdssd where gdssd.is_delete=0 and gdssd.goods_batch_code=a.goods_batch_code " +
                "and gdssd.generate_date=a.time_line and gdssd.warehouse_id=a.warehouse_id and gdssd.real_supply_chain_goods_settle_package_spec=a.supply_chain_goods_settle_package_spec  ) as indoor_subsidy_price"),
        new CustomSql("a.is_delete"), new CustomSql("a.create_user"), new CustomSql("a.update_user"), new CustomSql("a.create_time"), new CustomSql("a.update_time"))
        .addCustomFromTable("supplier_batch_goods_cost_price a");
        Iterator<String> iter = queryJS.fieldNames().iterator();
        while (iter.hasNext()) {
            String key = iter.next();
            try {
                String value = queryJS.getString(key);
                if (!value.equals("") && !value.equals("[]") && !value.equals("[all]")) {
                    String firstChar = value.substring(0, 1);
                    if (firstChar.equals("["))
                        value = value.substring(1, value.length() - 1);
                    value = value.replace(", ", "','");
                    // 添加where  InCondtion 子句
                    sq.getWhereClause().addCustomCondition(new InCondition(new CustomSql(key), value));
                }
            } catch (Exception e) {
                logger.info("createSql, 发生错误,错误原因: ", e.getMessage());
            }
        }
        return sq.validate() + " order by id desc limit " + query_limit + " offset " + query_offset;
    }



    /**
     * 查看期初明细 根据传入的查询参数获取满足条件的记录
     *
     * @param client
     * @param config
     * @param bodyJSObj
     * @return
     */
    public Future<JsonObject> getRecordsForInitialBatchDetial(PgPool client, JsonObject config, JsonObject bodyJSObj) {
        Promise<JsonObject> promise = Promise.promise();
        //最后返回的JsonObject对象
        JsonObject responseJSObj = new JsonObject();
        // 验证必须传的数值字段
        List<String> numeric_fields = Arrays.asList("warehouse_id", "aggregation_id", "supply_chain_goods_settle_package_spec", "page_size", "page_index");
        JsonObject numeric = this.commonRepository.verifyAllParamNumeric(bodyJSObj, numeric_fields);
        if (numeric != null) {
            promise.complete(numeric);
            return promise.future();
        }

        //从bodyJson中获取数据   各字段注释请参看数据库设计文档中数据表supplier_batch_goods_psi的说明\
        String warehouse_id = bodyJSObj.getString("warehouse_id");
        String aggregation_id = bodyJSObj.getString("aggregation_id");
        String supply_chain_goods_settle_package_spec = bodyJSObj.getString("supply_chain_goods_settle_package_spec");
        String goods_batch_code = bodyJSObj.getString("goods_batch_code");
        String time_line = bodyJSObj.getString("time_line");

        int page_size = Integer.valueOf(bodyJSObj.getString("page_size"));
        int page_index = Integer.valueOf(bodyJSObj.getString("page_index"));
        int query_offset = (page_index - 1) * page_size;
        int query_limit = page_size;

        JsonObject queryJSObj = new JsonObject();


        //首先根据聚合商品名获取对应的供应链商品名
        Set<Integer> supplys= new HashSet<>();
        this.commonRepository.getCon(client).onSuccess(conn -> {
                    //先根据聚合商品名获取对应的供应链商品名
                    JsonObject queryJSObj1 = new JsonObject();
                    queryJSObj1.put("aggregation_id", aggregation_id);
                    String get_supply_chain_gooods_id_sql = createSelectSqlGetSupplyChainGooodsId(queryJSObj1, query_offset, query_limit);
                    this.commonRepository.queryJsonArray(conn, get_supply_chain_gooods_id_sql, "getInitialBatchDetialRecords")
                            .onSuccess(resJSArray1 -> {
                                //        取出每条记录中含code字段的值放入对应的集合
                                if (resJSArray1.size()==0){
                                    JsonObject dataJSObj = new JsonObject();
                                    dataJSObj.put("total", 0).put("records","");
                                    responseJSObj.put("code", 200).put("msg", "获取记录成功").put("data", dataJSObj);
                                    promise.complete(responseJSObj);
                                }else {
                                    for (int i = 0; i < resJSArray1.size(); i++) {
                                        JsonObject jsonObj = resJSArray1.getJsonObject(i);
                                        Integer a = Integer.valueOf(jsonObj.getString("supply_chain_goods_id"));
                                        supplys.add(a);
                                    }

                                    //用于传递给构建sql的JsonObject
                                    queryJSObj.put("warehouse_id", warehouse_id);
                                    String supply_str="";
                                    Integer[] array = supplys.toArray(new Integer[supplys.size()]);
                                    for (int i = 0 ; i < array.length ; i ++){
                                        if (i==0){
                                            supply_str=supply_str+array[i];
                                        }else {
                                            supply_str=supply_str+","+array[i];
                                        }
                                    }
                                    queryJSObj.put("supply_chain_goods_id",supply_str);
                                    queryJSObj.put("supply_chain_goods_settle_package_spec", supply_chain_goods_settle_package_spec);
                                    queryJSObj.put("goods_batch_code", goods_batch_code);
                                    queryJSObj.put("time_line", time_line);
                                    queryJSObj.put("is_delete", 0);

                                    String sql = createSelectSqlForInitialBatchDetial(queryJSObj, query_offset, query_limit);

                                    //获取满足条件的记录总数
                                    String count_sql = this.commonRepository.createCountSql(queryJSObj, "initial_batch_detail");
                                    Future<Integer> getTotalFuture = this.commonRepository.getRecordsCount(conn, count_sql, "get_list_goods_batch_goods_psi");
                                    Future<JsonArray> future =  this.commonRepository.queryJsonArray(conn, sql, "getRecordsForInitialBatchDetial");
                                    CompositeFuture.join(Arrays.asList(getTotalFuture, future )).onComplete(ar2 -> {
                                        if (ar2.succeeded()) {
                                            int total_records = Integer.valueOf(ar2.result().list().get(0).toString());
                                            JsonObject dataJSObj = new JsonObject();
                                            JsonArray recordsJSArray=(JsonArray)ar2.result().list().get(1);
                                            dataJSObj.put("total", total_records).put("records",recordsJSArray);
                                            responseJSObj.put("code", 200).put("msg", "获取记录成功").put("data", dataJSObj);
                                            promise.complete(responseJSObj);
                                        } else {
                                            logger.info("获取记录数据发生错误: {}", ar2.cause().getMessage());
                                            promise.complete(CommonUtils.createExceptionJsonObject(ar2, "getRecordsForInitialBatchDetial", "获取供应商批次商品进销存参数失败"));
                                        }
                                    });
                                }
                            }).onFailure(err -> promise.complete(CommonUtils.createExceptionJsonObject(err, "getRecordsForInitialBatchDetial", "获取供应链商品参数失败")));
                }).
                onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getRecordsForInitialBatchDetial", "数据库连接失败")));
        return promise.future();
    }


    /**
     * 创建查询符合搜索条件记录的sql语句——查看期初明细
     *
     * @param queryJS      body参数对象
     * @param query_offset 页面偏移
     * @param query_limit  页面记录数
     * @return 查询符合搜索条件的sql语句
     */
    public String createSelectSqlForInitialBatchDetial(JsonObject queryJS, int query_offset, int query_limit) {
        SelectQuery sq = new SelectQuery();
        sq.addCustomColumns(new CustomSql("id"), new CustomSql("supply_chain_goods_id"), new CustomSql("supply_chain_goods_settle_package_spec"), new CustomSql("goods_batch_code"),
                        new CustomSql("supplier_id"), new CustomSql("real_settlement_price"), new CustomSql("transport_price"), new CustomSql("land_price"),
                        new CustomSql("inventory_number"),new CustomSql("  (select a.supply_chain_goods_name " +
                                "        from supply_chain_goods a " +
                                "        where a.is_delete=0 " +
                                "        and  a.id=supply_chain_goods_id) as supply_chain_goods_name "),
                        new CustomSql("     (select b.supplier_name " +
                                "        from supplier_info b " +
                                "        where b.is_delete=0 " +
                                "          and b.id=supplier_id) as supplier_name "),
                        new CustomSql("  (select w.warehouse_name " +
                                "        from warehouse w " +
                                "        where w.is_delete=0 " +
                                "          and w.id=warehouse_id) as warehouse_name "),
                        new CustomSql("inventory_weight"), new CustomSql("time_line"),new CustomSql("inventory_land_acount"), new CustomSql("warehouse_id"),
                        new CustomSql("is_delete"), new CustomSql("create_user"), new CustomSql("update_user"), new CustomSql("create_time"), new CustomSql("update_time"))
                .addCustomFromTable("initial_batch_detail");
        Iterator<String> iter = queryJS.fieldNames().iterator();
        while (iter.hasNext()) {
            String key = iter.next();
            try {
                String value = queryJS.getString(key);
                if (!value.equals("") && !value.equals("[]") && !value.equals("[all]")) {
                    String firstChar = value.substring(0, 1);
                    if (firstChar.equals("["))
                        value = value.substring(1, value.length() - 1);
                    value = value.replace(", ", "','");
                    // 添加where  InCondtion 子句
                    sq.getWhereClause().addCustomCondition(new InCondition(new CustomSql(key), value));
                }
            } catch (Exception e) {
                logger.info("createSql, 发生错误,错误原因: ", e.getMessage());
            }
        }
        return sq.validate() + " order by id desc limit " + query_limit + " offset " + query_offset;
    }


    /**
     * 查看销售退库入库单明细 根据传入的查询参数获取满足条件的记录
     *
     * @param client
     * @param config
     * @param bodyJSObj
     * @return
     */
    public Future<JsonObject> getRecordsForSalesReturnWarehouseDetial(PgPool client, JsonObject config, JsonObject bodyJSObj) {
        Promise<JsonObject> promise = Promise.promise();
        //最后返回的JsonObject对象
        JsonObject responseJSObj = new JsonObject();
        // 验证必须传的数值字段
        List<String> numeric_fields = Arrays.asList("warehouse_id", "aggregation_id", "supply_chain_goods_settle_package_spec", "page_size", "page_index");
        JsonObject numeric = this.commonRepository.verifyAllParamNumeric(bodyJSObj, numeric_fields);
        if (numeric != null) {
            promise.complete(numeric);
            return promise.future();
        }

        //从bodyJson中获取数据   各字段注释请参看数据库设计文档中数据表supplier_batch_goods_psi的说明\
        String warehouse_id = bodyJSObj.getString("warehouse_id");
        String aggregation_id = bodyJSObj.getString("aggregation_id");
        String supply_chain_goods_settle_package_spec = bodyJSObj.getString("supply_chain_goods_settle_package_spec");
        String goods_batch_code = bodyJSObj.getString("goods_batch_code");
        String delivery_date = bodyJSObj.getString("delivery_date");

        int page_size = Integer.valueOf(bodyJSObj.getString("page_size"));
        int page_index = Integer.valueOf(bodyJSObj.getString("page_index"));
        int query_offset = (page_index - 1) * page_size;
        int query_limit = page_size;

        JsonObject queryJSObj = new JsonObject();

        //首先根据聚合商品名获取对应的供应链商品名
        Set<Integer> supplys= new HashSet<>();
        this.commonRepository.getCon(client).onSuccess(conn -> {
                    //先根据聚合商品名获取对应的供应链商品名
                    JsonObject queryJSObj1 = new JsonObject();
                    queryJSObj1.put("aggregation_id", aggregation_id);
                    String get_supply_chain_gooods_id_sql = createSelectSqlGetSupplyChainGooodsId(queryJSObj1, query_offset, query_limit);
                    this.commonRepository.queryJsonArray(conn, get_supply_chain_gooods_id_sql, "getInitialBatchDetialRecords")
                            .onSuccess(resJSArray1 -> {
                                //        取出每条记录中含code字段的值放入对应的集合
                                if (resJSArray1.size()==0){
                                    JsonObject dataJSObj = new JsonObject();
                                    dataJSObj.put("total", 0).put("records","");
                                    responseJSObj.put("code", 200).put("msg", "获取记录成功").put("data", dataJSObj);
                                    promise.complete(responseJSObj);
                                }else {
                                    for (int i = 0; i < resJSArray1.size(); i++) {
                                        JsonObject jsonObj = resJSArray1.getJsonObject(i);
                                        Integer a = Integer.valueOf(jsonObj.getString("supply_chain_goods_id"));
                                        supplys.add(a);
                                    }
                                    //用于传递给构建sql的JsonObject
                                    queryJSObj.put("warehouse_id", warehouse_id);
                                    String supply_str="";
                                    Integer[] array = supplys.toArray(new Integer[supplys.size()]);
                                    for (int i = 0 ; i < array.length ; i ++){
                                        if (i==0){
                                            supply_str=supply_str+array[i];
                                        }else {
                                            supply_str=supply_str+","+array[i];
                                        }
                                    }
                                    queryJSObj.put("supply_chain_goods_id", supply_str);
                                    queryJSObj.put("supply_chain_goods_settle_package_spec", supply_chain_goods_settle_package_spec);
                                    queryJSObj.put("goods_batch_code", goods_batch_code);
                                    queryJSObj.put("is_delete", 0);
                                    queryJSObj.put("delivery_date", delivery_date);

                                    String sql = createSelectSqlForSalesReturnWarehouseDetail(queryJSObj, query_offset, query_limit);

                                    //获取满足条件的记录总数
                                    String count_sql = this.commonRepository.createCountSql(queryJSObj, "sales_return_warehouse_detail");
                                    Future<Integer> getTotalFuture = this.commonRepository.getRecordsCount(conn, count_sql, "getRecordsForSalesReturnWarehouseDetial");
                                    Future<JsonArray> future =  this.commonRepository.queryJsonArray(conn, sql, "getRecordsForSalesReturnWarehouseDetial");
                                    CompositeFuture.join(Arrays.asList(getTotalFuture, future )).onComplete(ar2 -> {
                                        if (ar2.succeeded()) {
                                            int total_records = Integer.valueOf(ar2.result().list().get(0).toString());
                                            JsonObject dataJSObj = new JsonObject();
                                            JsonArray recordsJSArray=(JsonArray)ar2.result().list().get(1);
                                            dataJSObj.put("total", total_records).put("records",recordsJSArray);
                                            responseJSObj.put("code", 200).put("msg", "获取记录成功").put("data", dataJSObj);
                                            promise.complete(responseJSObj);
                                        } else {
                                            logger.info("获取记录数据发生错误: {}", ar2.cause().getMessage());
                                            promise.complete(CommonUtils.createExceptionJsonObject(ar2, "getRecordsForSalesReturnWarehouseDetial", "获取销售退货入库单明细失败"));
                                        }
                                    });
                                }
                            }).onFailure(err -> promise.complete(CommonUtils.createExceptionJsonObject(err, "getRecordsForSalesReturnWarehouseDetial", "获取销售退货入库单明细失败")));
                }).
                onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getRecordsForSalesReturnWarehouseDetial", "数据库连接失败")));
        return promise.future();
    }


    /**
     * 创建查询符合搜索条件记录的sql语句——查看销售退库入库单明细
     *
     * @param queryJS      body参数对象
     * @param query_offset 页面偏移
     * @param query_limit  页面记录数
     * @return 查询符合搜索条件的sql语句
     */
    public String createSelectSqlForSalesReturnWarehouseDetail(JsonObject queryJS, int query_offset, int query_limit) {
        SelectQuery sq = new SelectQuery();
        sq.addCustomColumns(new CustomSql("id"), new CustomSql("delivery_date"), new CustomSql("shift_delivery_plan_code"), new CustomSql("sales_return_warehouse_code"),
                        new CustomSql("list_goods_id"),new CustomSql("( select lg.list_goods_name " +
                                "from list_goods lg where lg.is_delete=0 and lg.id=list_goods_id ) as list_goods_name"),
                        new CustomSql("list_package_spec"), new CustomSql("sales_package_spec_id"),
                        new CustomSql("(select spst.package_spec_name from sales_package_spec spst where spst.is_delete=0 " +
                                "and spst.id=sales_package_spec_id ) as package_spec_name"),
                        new CustomSql("goods_batch_code"), new CustomSql("supply_chain_goods_id"),
                        new CustomSql("(select scg.supply_chain_goods_name from supply_chain_goods scg  where scg.is_delete=0 " +
                                "and scg.id= supply_chain_goods_id ) as supply_chain_goods_name "),
                        new CustomSql("supply_chain_goods_settle_package_spec"), new CustomSql("buyer_name"), new CustomSql("buyer_id"), new CustomSql("order_code"), new CustomSql("retrun_number"), new CustomSql("warehouse_id"),
                        new CustomSql("retrun_weight"), new CustomSql("retrun_warehouse_weight"), new CustomSql("retrun_warehouse_number"), new CustomSql("return_loss_number"), new CustomSql("return_loss_weight"),
                        new CustomSql("is_delete"), new CustomSql("create_user"), new CustomSql("update_user"), new CustomSql("create_time"), new CustomSql("update_time"))
                .addCustomFromTable("sales_return_warehouse_detail");
        Iterator<String> iter = queryJS.fieldNames().iterator();
        while (iter.hasNext()) {
            String key = iter.next();
            try {
                String value = queryJS.getString(key);
                if (!value.equals("") && !value.equals("[]") && !value.equals("[all]")) {
                    String firstChar = value.substring(0, 1);
                    if (firstChar.equals("["))
                        value = value.substring(1, value.length() - 1);
                    value = value.replace(", ", "','");
                    // 添加where  InCondtion 子句
                    sq.getWhereClause().addCustomCondition(new InCondition(new CustomSql(key), value));
                }
            } catch (Exception e) {
                logger.info("createSql, 发生错误,错误原因: ", e.getMessage());
            }
        }
        return sq.validate() + " order by id desc limit " + query_limit + " offset " + query_offset;
    }
    /**
     * 查看车次配送计划完工单明细 根据传入的查询参数获取满足条件的记录
     *
     * @param client
     * @param config
     * @param bodyJSObj
     * @return
     */
    public Future<JsonObject> getRecordsForShiftDeliveryPlanCompletionDetail(PgPool client, JsonObject config, JsonObject bodyJSObj) {
        Promise<JsonObject> promise = Promise.promise();
        //最后返回的JsonObject对象
        JsonObject responseJSObj = new JsonObject();
        // 验证必须传的数值字段
        List<String> numeric_fields = Arrays.asList("warehouse_id", "aggregation_id", "supply_chain_goods_settle_package_spec", "page_size", "page_index");
        JsonObject numeric = this.commonRepository.verifyAllParamNumeric(bodyJSObj, numeric_fields);
        if (numeric != null) {
            promise.complete(numeric);
            return promise.future();
        }

        //从bodyJson中获取数据   各字段注释请参看数据库设计文档中数据表list_goods_batch_goods_psi的说明\
        String warehouse_id = bodyJSObj.getString("warehouse_id");
        String aggregation_id = bodyJSObj.getString("aggregation_id");
        String supply_chain_goods_settle_package_spec = bodyJSObj.getString("supply_chain_goods_settle_package_spec");
        String goods_batch_code = bodyJSObj.getString("goods_batch_code");
        String delivery_date = bodyJSObj.getString("delivery_date");

        int page_size = Integer.valueOf(bodyJSObj.getString("page_size"));
        int page_index = Integer.valueOf(bodyJSObj.getString("page_index"));
        int query_offset = (page_index - 1) * page_size;
        int query_limit = page_size;
        //用于传递给构建sql的JsonObject
        JsonObject queryJSObj = new JsonObject();

        //首先根据聚合商品名获取对应的供应链商品名
        Set<Integer> supplys= new HashSet<>();
        this.commonRepository.getCon(client).onSuccess(conn -> {
            //先根据聚合商品名获取对应的供应链商品名
            JsonObject queryJSObj1 = new JsonObject();
            queryJSObj1.put("aggregation_id", aggregation_id);
            String get_supply_chain_gooods_id_sql = createSelectSqlGetSupplyChainGooodsId(queryJSObj1, query_offset, query_limit);
            this.commonRepository.queryJsonArray(conn, get_supply_chain_gooods_id_sql, "getInitialBatchDetialRecords")
                    .onSuccess(resJSArray1 -> {
                        //        取出每条记录中含code字段的值放入对应的集合
                        if (resJSArray1.size()==0){
                            JsonObject dataJSObj = new JsonObject();
                            dataJSObj.put("total", 0).put("records","");
                            responseJSObj.put("code", 200).put("msg", "获取记录成功").put("data", dataJSObj);
                            promise.complete(responseJSObj);
                        }else {
                            for (int i = 0; i < resJSArray1.size(); i++) {
                                JsonObject jsonObj = resJSArray1.getJsonObject(i);
                                Integer a = Integer.valueOf(jsonObj.getString("supply_chain_goods_id"));
                                supplys.add(a);
                            }
                            //用于传递给构建sql的JsonObject
                            queryJSObj.put("warehouse_id", warehouse_id);
                            String supply_str="";
                            Integer[] array = supplys.toArray(new Integer[supplys.size()]);
                            for (int i = 0 ; i < array.length ; i ++){
                                if (i==0){
                                    supply_str=supply_str+array[i];
                                }else {
                                    supply_str=supply_str+","+array[i];
                                }
                            }
                            queryJSObj.put("supply_chain_goods_id",supply_str);
                            queryJSObj.put("supply_chain_goods_settle_package_spec", supply_chain_goods_settle_package_spec);
                            queryJSObj.put("goods_batch_code", goods_batch_code);
                            queryJSObj.put("is_delete", 0);
                            queryJSObj.put("delivery_date", delivery_date);
                            String sql = createSelectSqlForShiftDeliveryPlanCompletionDetail(queryJSObj, query_offset, query_limit);
                            //获取满足条件的记录总数
                            String count_sql = this.commonRepository.createCountSql(queryJSObj, "shift_delivery_plan_completion_detail");
                            Future<Integer> getTotalFuture = this.commonRepository.getRecordsCount(conn, count_sql, "getRecordsForShiftDeliveryPlanCompletionDetail");
                            Future<JsonArray> future =  this.commonRepository.queryJsonArray(conn, sql, "getRecordsForShiftDeliveryPlanCompletionDetail");
                            CompositeFuture.join(Arrays.asList(getTotalFuture, future )).onComplete(ar2 -> {
                                if (ar2.succeeded()) {
                                    int total_records = Integer.valueOf(ar2.result().list().get(0).toString());
                                    JsonObject dataJSObj = new JsonObject();
                                    JsonArray recordsJSArray=(JsonArray)ar2.result().list().get(1);
                                    dataJSObj.put("total", total_records).put("records",recordsJSArray);
                                    responseJSObj.put("code", 200).put("msg", "获取记录成功").put("data", dataJSObj);
                                    promise.complete(responseJSObj);
                                } else {
                                    logger.info("获取记录数据发生错误: {}", ar2.cause().getMessage());
                                    promise.complete(CommonUtils.createExceptionJsonObject(ar2, "getRecordsForShiftDeliveryPlanCompletionDetail", "获取车次配送计划完工单明细失败"));
                                }
                            });
                        }
                    }).
                    onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getRecordsForShiftDeliveryPlanCompletionDetail", "数据库连接失败")));
        });
        return promise.future();
    }

    /**
     * 创建查询符合搜索条件记录的sql语句——查看车次配送计划完工单明细
     *
     * @param queryJS      body参数对象
     * @param query_offset 页面偏移
     * @param query_limit  页面记录数
     * @return 查询符合搜索条件的sql语句
     */
    public String createSelectSqlForShiftDeliveryPlanCompletionDetail(JsonObject queryJS, int query_offset, int query_limit) {
        SelectQuery sq = new SelectQuery();
        sq.addCustomColumns(new CustomSql("id"), new CustomSql("delivery_date"), new CustomSql("shift_delivery_plan_code"), new CustomSql("shift_delivery_plan_completion_code"),
                        new CustomSql("list_goods_id"), new CustomSql("( select lg.list_goods_name " +
                                "from list_goods lg where lg.is_delete=0 and lg.id=list_goods_id ) as list_goods_name"),
                        new CustomSql("(select spst.package_spec_name from sales_package_spec spst where spst.is_delete=0 " +
                                "and spst.id=sales_package_spec_id ) as package_spec_name"),
                        new CustomSql("(select scg.supply_chain_goods_name from supply_chain_goods scg  where scg.is_delete=0 " +
                                "and scg.id= supply_chain_goods_id ) as supply_chain_goods_name "),
                        new CustomSql("list_package_spec"), new CustomSql("sales_package_spec_id"),
                        new CustomSql("goods_batch_code"), new CustomSql("supply_chain_goods_id"),
                        new CustomSql("supply_chain_goods_settle_package_spec"), new CustomSql("buyer_name"), new CustomSql("buyer_id"), new CustomSql("order_code"), new CustomSql("order_number"),
                        new CustomSql("order_weight"), new CustomSql("shift_delivery_plan_number"), new CustomSql("shift_delivery_plan_completion_number"), new CustomSql("diff_number"),
                        new CustomSql("shift_delivery_plan_weight"), new CustomSql("shift_delivery_plan_completion_weight"), new CustomSql("diff_weight"), new CustomSql("warehouse_id"),
                        new CustomSql("is_delete"), new CustomSql("create_user"), new CustomSql("update_user"), new CustomSql("create_time"), new CustomSql("update_time"))
                .addCustomFromTable("shift_delivery_plan_completion_detail");
        Iterator<String> iter = queryJS.fieldNames().iterator();
        while (iter.hasNext()) {
            String key = iter.next();
            try {
                String value = queryJS.getString(key);
                if (!value.equals("") && !value.equals("[]") && !value.equals("[all]")) {
                    String firstChar = value.substring(0, 1);
                    if (firstChar.equals("["))
                        value = value.substring(1, value.length() - 1);
                    value = value.replace(", ", "','");
                    // 添加where  InCondtion 子句
                    sq.getWhereClause().addCustomCondition(new InCondition(new CustomSql(key), value));
                }
            } catch (Exception e) {
                logger.info("createSql, 发生错误,错误原因: ", e.getMessage());
            }
        }
        return sq.validate() + " order by id desc limit " + query_limit + " offset " + query_offset;
    }


    /**
     * 查看自提完工单明细 根据传入的查询参数获取满足条件的记录
     *
     * @param client
     * @param config
     * @param bodyJSObj
     * @return
     */
    public Future<JsonObject> getRecordsForPickupCompletionDetail(PgPool client, JsonObject config, JsonObject bodyJSObj) {
        Promise<JsonObject> promise = Promise.promise();
        //最后返回的JsonObject对象
        JsonObject responseJSObj = new JsonObject();
        // 验证必须传的数值字段
        List<String> numeric_fields = Arrays.asList("warehouse_id", "aggregation_id", "supply_chain_goods_settle_package_spec", "page_size", "page_index");
        JsonObject numeric = this.commonRepository.verifyAllParamNumeric(bodyJSObj, numeric_fields);
        if (numeric != null) {
            promise.complete(numeric);
            return promise.future();
        }

        //从bodyJson中获取数据   各字段注释请参看数据库设计文档中数据表list_goods_batch_goods_psi的说明\
        String warehouse_id = bodyJSObj.getString("warehouse_id");
        String aggregation_id = bodyJSObj.getString("aggregation_id");
        String supply_chain_goods_settle_package_spec = bodyJSObj.getString("supply_chain_goods_settle_package_spec");
        String goods_batch_code = bodyJSObj.getString("goods_batch_code");
        String trade_date = bodyJSObj.getString("trade_date");

        int page_size = Integer.valueOf(bodyJSObj.getString("page_size"));
        int page_index = Integer.valueOf(bodyJSObj.getString("page_index"));
        int query_offset = (page_index - 1) * page_size;
        int query_limit = page_size;
        //用于传递给构建sql的JsonObject
        JsonObject queryJSObj = new JsonObject();

        //首先根据聚合商品名获取对应的供应链商品名
        Set<Integer> supplys= new HashSet<>();
        this.commonRepository.getCon(client).onSuccess(conn -> {
                    //先根据聚合商品名获取对应的供应链商品名
                    JsonObject queryJSObj1 = new JsonObject();
                    queryJSObj1.put("aggregation_id", aggregation_id);
                    String get_supply_chain_gooods_id_sql = createSelectSqlGetSupplyChainGooodsId(queryJSObj1, query_offset, query_limit);
                    this.commonRepository.queryJsonArray(conn, get_supply_chain_gooods_id_sql, "getInitialBatchDetialRecords")
                            .onSuccess(resJSArray1 -> {
                                //        取出每条记录中含code字段的值放入对应的集合
                                if (resJSArray1.size()==0){
                                    JsonObject dataJSObj = new JsonObject();
                                    dataJSObj.put("total", 0).put("records","");
                                    responseJSObj.put("code", 200).put("msg", "获取记录成功").put("data", dataJSObj);
                                    promise.complete(responseJSObj);
                                }else {
                                    for (int i = 0; i < resJSArray1.size(); i++) {
                                        JsonObject jsonObj = resJSArray1.getJsonObject(i);
                                        Integer a = Integer.valueOf(jsonObj.getString("supply_chain_goods_id"));
                                        supplys.add(a);
                                    }

                                    //用于传递给构建sql的JsonObject
                                    queryJSObj.put("warehouse_id", warehouse_id);
                                    String supply_str="";
                                    Integer[] array = supplys.toArray(new Integer[supplys.size()]);
                                    for (int i = 0 ; i < array.length ; i ++){
                                        if (i==0){
                                            supply_str=supply_str+array[i];
                                        }else {
                                            supply_str=supply_str+","+array[i];
                                        }
                                    }
                                    queryJSObj.put("supply_chain_goods_id", supply_str);
                                    queryJSObj.put("supply_chain_goods_settle_package_spec", supply_chain_goods_settle_package_spec);
                                    queryJSObj.put("goods_batch_code", goods_batch_code);
                                    queryJSObj.put("is_delete", 0);
                                    queryJSObj.put("trade_date", trade_date);
                                    String sql = createSelectSqlForPickupCompletionDetail(queryJSObj, query_offset, query_limit);
                                    //获取满足条件的记录总数
                                    String count_sql = this.commonRepository.createCountSql(queryJSObj, "pickup_completion_detail");
                                    Future<Integer> getTotalFuture = this.commonRepository.getRecordsCount(conn, count_sql, "getRecordsForPickupCompletionDetail");
                                    Future<JsonArray> future =  this.commonRepository.queryJsonArray(conn, sql, "getRecordsForPickupCompletionDetail");
                                    CompositeFuture.join(Arrays.asList(getTotalFuture, future )).onComplete(ar2 -> {
                                        if (ar2.succeeded()) {
                                            int total_records = Integer.valueOf(ar2.result().list().get(0).toString());
                                            JsonObject dataJSObj = new JsonObject();
                                            JsonArray recordsJSArray=(JsonArray)ar2.result().list().get(1);
                                            dataJSObj.put("total", total_records).put("records",recordsJSArray);
                                            responseJSObj.put("code", 200).put("msg", "获取记录成功").put("data", dataJSObj);
                                            promise.complete(responseJSObj);
                                        } else {
                                            logger.info("获取记录数据发生错误: {}", ar2.cause().getMessage());
                                            promise.complete(CommonUtils.createExceptionJsonObject(ar2, "getRecordsForPickupCompletionDetail", "获取自提完工单明细失败"));
                                        }
                                    });
                                }
                            }).onFailure(err -> promise.complete(CommonUtils.createExceptionJsonObject(err, "getRecordsForPickupCompletionDetail", "获取自提完工单明细失败")));
                }).
                onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getRecordsForPickupCompletionDetail", "数据库连接失败")));
        return promise.future();
    }

    /**
     * 创建查询符合搜索条件记录的sql语句——查看自提完工单明细
     *
     * @param queryJS      body参数对象
     * @param query_offset 页面偏移
     * @param query_limit  页面记录数
     * @return 查询符合搜索条件的sql语句
     */
    public String createSelectSqlForPickupCompletionDetail(JsonObject queryJS, int query_offset, int query_limit) {
        SelectQuery sq = new SelectQuery();
        sq.addCustomColumns(new CustomSql("id"), new CustomSql("trade_date"), new CustomSql("pickup_completion_code"), new CustomSql("warehouse_id"),
                        new CustomSql("(select w.warehouse_name " +
                                " from warehouse w " +
                                " where w.is_delete=0 " +
                                " and w.id=warehouse_id) as warehouse_name "),
                        new CustomSql("buyer_name"), new CustomSql("buyer_id"), new CustomSql("order_code"), new CustomSql("list_goods_id"), new CustomSql("list_package_spec"), new CustomSql("sales_package_spec_id"),
                        new CustomSql("order_number"), new CustomSql("order_weight"), new CustomSql("goods_batch_code"), new CustomSql("supply_chain_goods_id"),
                        new CustomSql("( select lg.list_goods_name " +
                                "from list_goods lg where lg.is_delete=0 and lg.id=list_goods_id ) as list_goods_name"),
                        new CustomSql("(select spst.package_spec_name from sales_package_spec spst where spst.is_delete=0 " +
                                "and spst.id=sales_package_spec_id ) as package_spec_name"),
                        new CustomSql("(select scg.supply_chain_goods_name from supply_chain_goods scg  where scg.is_delete=0 " +
                                "and scg.id= supply_chain_goods_id ) as supply_chain_goods_name "),
                        new CustomSql("supply_chain_goods_settle_package_spec"), new CustomSql("pickup_number"), new CustomSql("diff_number"), new CustomSql("pickup_weight"), new CustomSql("diff_weight"),
                        new CustomSql("is_delete"), new CustomSql("create_user"), new CustomSql("update_user"), new CustomSql("create_time"), new CustomSql("update_time"))
                .addCustomFromTable("pickup_completion_detail");
        Iterator<String> iter = queryJS.fieldNames().iterator();
        while (iter.hasNext()) {
            String key = iter.next();
            try {
                String value = queryJS.getString(key);
                if (!value.equals("") && !value.equals("[]") && !value.equals("[all]")) {
                    String firstChar = value.substring(0, 1);
                    if (firstChar.equals("["))
                        value = value.substring(1, value.length() - 1);
                    value = value.replace(", ", "','");
                    // 添加where  InCondtion 子句
                    sq.getWhereClause().addCustomCondition(new InCondition(new CustomSql(key), value));
                }
            } catch (Exception e) {
                logger.info("createSql, 发生错误,错误原因: ", e.getMessage());
            }
        }
        return sq.validate() + " order by id desc limit " + query_limit + " offset " + query_offset;
    }

    /**
     * 查看采购退货单明细 根据传入的查询参数获取满足条件的记录
     *
     * @param client
     * @param config
     * @param bodyJSObj
     * @return
     */
    public Future<JsonObject> getRecordsForPurchaseReturnDetail(PgPool client, JsonObject config, JsonObject bodyJSObj) {
        Promise<JsonObject> promise = Promise.promise();
        //最后返回的JsonObject对象
        JsonObject responseJSObj = new JsonObject();
        // 验证必须传的数值字段
        List<String> numeric_fields = Arrays.asList("warehouse_id", "aggregation_id", "page_size", "page_index");
        JsonObject numeric = this.commonRepository.verifyAllParamNumeric(bodyJSObj, numeric_fields);
        if (numeric != null) {
            promise.complete(numeric);
            return promise.future();
        }

        //从bodyJson中获取数据   各字段注释请参看数据库设计文档中数据表purchase_return_detail的说明\
        String warehouse_id = bodyJSObj.getString("warehouse_id");
        String aggregation_id = bodyJSObj.getString("aggregation_id");
        String purchase_return_date = bodyJSObj.getString("purchase_return_date");

        int page_size = Integer.valueOf(bodyJSObj.getString("page_size"));
        int page_index = Integer.valueOf(bodyJSObj.getString("page_index"));
        int query_offset = (page_index - 1) * page_size;
        int query_limit = page_size;
        //用于传递给构建sql的JsonObject
        JsonObject queryJSObj = new JsonObject();

        //定义多个set，存储每一个 initial_batch_detail 中多个code
        Set<String> warehouse_id_list_set = new HashSet<String>();
        Set<String> supply_chain_goods_id_list_set = new HashSet<String>();
        Set<String> supplier_id_list_set = new HashSet<String>();
        //首先根据聚合商品名获取对应的供应链商品名
        Set<Integer> supplys= new HashSet<>();
        this.commonRepository.getCon(client).onSuccess(conn -> {
                    //先根据聚合商品名获取对应的供应链商品名
                    JsonObject queryJSObj1 = new JsonObject();
                    queryJSObj1.put("aggregation_id", aggregation_id);
                    String get_supply_chain_gooods_id_sql = createSelectSqlGetSupplyChainGooodsId(queryJSObj1, query_offset, query_limit);
                    this.commonRepository.queryJsonArray(conn, get_supply_chain_gooods_id_sql, "getInitialBatchDetialRecords")
                            .onSuccess(resJSArray1 -> {
                                //        取出每条记录中含code字段的值放入对应的集合
                                if (resJSArray1.size()==0){
                                    JsonObject dataJSObj = new JsonObject();
                                    dataJSObj.put("total", 0).put("records","");
                                    responseJSObj.put("code", 200).put("msg", "获取记录成功").put("data", dataJSObj);
                                    promise.complete(responseJSObj);
                                }else {
                                    for (int i = 0; i < resJSArray1.size(); i++) {
                                        JsonObject jsonObj = resJSArray1.getJsonObject(i);
                                        Integer a = Integer.valueOf(jsonObj.getString("supply_chain_goods_id"));
                                        supplys.add(a);
                                    }
                                    //用于传递给构建sql的JsonObject
                                    queryJSObj.put("warehouse_id", warehouse_id);
                                    String supply_str="";
                                    Integer[] array = supplys.toArray(new Integer[supplys.size()]);
                                    for (int i = 0 ; i < array.length ; i ++){
                                        if (i==0){
                                            supply_str=supply_str+array[i];
                                        }else {
                                            supply_str=supply_str+","+array[i];
                                        }
                                    }
                                    queryJSObj.put("supply_chain_goods_id", supply_str);
                                    queryJSObj.put("is_delete", 0);
                                    queryJSObj.put("purchase_return_date", purchase_return_date);
                                    String sql = createSelectSqlForPurchaseReturnDetail(queryJSObj, query_offset, query_limit);
                                    //获取满足条件的记录总数
                                    String count_sql = this.commonRepository.createCountSql(queryJSObj, "purchase_return_detail");
                                    Future<Integer> getTotalFuture = this.commonRepository.getRecordsCount(conn, count_sql, "getRecordsForPurchaseReturnDetail");
                                    Future<JsonArray> future =  this.commonRepository.queryJsonArray(conn, sql, "getRecordsForPurchaseReturnDetail");
                                    CompositeFuture.join(Arrays.asList(getTotalFuture, future )).onComplete(ar2 -> {
                                        if (ar2.succeeded()) {
                                            int total_records = Integer.valueOf(ar2.result().list().get(0).toString());
                                            JsonObject dataJSObj = new JsonObject();
                                            JsonArray recordsJSArray=(JsonArray)ar2.result().list().get(1);
                                            dataJSObj.put("total", total_records).put("records",recordsJSArray);
                                            responseJSObj.put("code", 200).put("msg", "获取记录成功").put("data", dataJSObj);
                                            promise.complete(responseJSObj);
                                        } else {
                                            logger.info("获取记录数据发生错误: {}", ar2.cause().getMessage());
                                            promise.complete(CommonUtils.createExceptionJsonObject(ar2, "getRecordsForPurchaseReturnDetail", "获取采购退货单明细失败"));
                                        }
                                    });
                                }
                            }).onFailure(err -> promise.complete(CommonUtils.createExceptionJsonObject(err, "getRecordsForPurchaseReturnDetail", "获取采购退货单明细失败")));
                }).
                onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getRecordsForPurchaseReturnDetail", "数据库连接失败")));
        return promise.future();
    }

    /**
     * 创建查询符合搜索条件记录的sql语句——查看采购退货单明细
     *
     * @param queryJS      body参数对象
     * @param query_offset 页面偏移
     * @param query_limit  页面记录数
     * @return 查询符合搜索条件的sql语句
     */
    public String createSelectSqlForPurchaseReturnDetail(JsonObject queryJS, int query_offset, int query_limit) {
        SelectQuery sq = new SelectQuery();
        sq.addCustomColumns(new CustomSql("id"), new CustomSql("purchase_return_code"), new CustomSql("goods_batch_code"),
                        new CustomSql("supply_chain_goods_id"),new CustomSql("purchase_return_date"),
                        new CustomSql("( select lg.supplier_name " +
                                "from supplier_info lg where lg.is_delete=0 and lg.id=supplier_id ) as supplier_name"),
                        new CustomSql("(select scg.supply_chain_goods_name from supply_chain_goods scg  where scg.is_delete=0 " +
                                "and scg.id= supply_chain_goods_id ) as supply_chain_goods_name "),
                        new CustomSql("supplier_id"), new CustomSql("purchase_return_weight"), new CustomSql("purchase_return_number"), new CustomSql("warehouse_id"),
                        new CustomSql("(select w.warehouse_name from warehouse w  where w.is_delete=0  and w.id=warehouse_id ) as warehouse_name"),
                        new CustomSql("is_delete"), new CustomSql("create_user"), new CustomSql("update_user"), new CustomSql("create_time"), new CustomSql("update_time"))
                .addCustomFromTable("purchase_return_detail");
        Iterator<String> iter = queryJS.fieldNames().iterator();
        while (iter.hasNext()) {
            String key = iter.next();
            try {
                String value = queryJS.getString(key);
                if (!value.equals("") && !value.equals("[]") && !value.equals("[all]")) {
                    String firstChar = value.substring(0, 1);
                    if (firstChar.equals("["))
                        value = value.substring(1, value.length() - 1);
                    value = value.replace(", ", "','");
                    // 添加where  InCondtion 子句
                    sq.getWhereClause().addCustomCondition(new InCondition(new CustomSql(key), value));
                }
            } catch (Exception e) {
                logger.info("createSql, 发生错误,错误原因: ", e.getMessage());
            }
        }
        return sq.validate() + " order by id desc limit " + query_limit + " offset " + query_offset;
    }

    /**
     * 查看期末明细 根据传入的查询参数获取满足条件的记录
     *
     * @param client
     * @param config
     * @param bodyJSObj
     * @return
     */
    public Future<JsonObject> getRecordsForEndBatchDetial(PgPool client, JsonObject config, JsonObject bodyJSObj) {
        Promise<JsonObject> promise = Promise.promise();
        //最后返回的JsonObject对象
        JsonObject responseJSObj = new JsonObject();
        // 验证必须传的数值字段
        List<String> numeric_fields = Arrays.asList("warehouse_id", "aggregation_id", "supply_chain_goods_settle_package_spec" ,"page_size", "page_index");
        JsonObject numeric = this.commonRepository.verifyAllParamNumeric(bodyJSObj, numeric_fields);
        if (numeric != null) {
            promise.complete(numeric);
            return promise.future();
        }

        //从bodyJson中获取数据   各字段注释请参看数据库设计文档中数据表supplier_batch_goods_psi的说明\
        String warehouse_id = bodyJSObj.getString("warehouse_id");
        String aggregation_id = bodyJSObj.getString("aggregation_id");
        String time_line = bodyJSObj.getString("time_line");
        String supply_chain_goods_settle_package_spec = bodyJSObj.getString("supply_chain_goods_settle_package_spec");
        String goods_batch_code = bodyJSObj.getString("goods_batch_code");
        int page_size = Integer.valueOf(bodyJSObj.getString("page_size"));
        int page_index = Integer.valueOf(bodyJSObj.getString("page_index"));
        int query_offset = (page_index - 1) * page_size;
        int query_limit = page_size;

        JsonObject queryJSObj = new JsonObject();

        //定义多个set，存储每一个 end_batch_detial 中多个code
        Set<String> warehouse_id_list_set = new HashSet<String>();
        Set<String> supplier_id_list_set = new HashSet<String>();
        Set<String> supply_chain_goods_id_list_set = new HashSet<String>();
        //首先根据聚合商品名获取对应的供应链商品名
        Set<Integer> supplys= new HashSet<>();
        this.commonRepository.getCon(client).onSuccess(conn -> {
                    //先根据聚合商品名获取对应的供应链商品名
                    JsonObject queryJSObj1 = new JsonObject();
                    queryJSObj1.put("aggregation_id", aggregation_id);
                    String get_supply_chain_gooods_id_sql = createSelectSqlGetSupplyChainGooodsId(queryJSObj1, query_offset, query_limit);
                    this.commonRepository.queryJsonArray(conn, get_supply_chain_gooods_id_sql, "getInitialBatchDetialRecords")
                            .onSuccess(resJSArray1 -> {
                                //        取出每条记录中含code字段的值放入对应的集合
                                if (resJSArray1.size()==0){
                                    JsonObject dataJSObj = new JsonObject();
                                    dataJSObj.put("total", 0).put("records","");
                                    responseJSObj.put("code", 200).put("msg", "获取记录成功").put("data", dataJSObj);
                                    promise.complete(responseJSObj);
                                }else {
                                    for (int i = 0; i < resJSArray1.size(); i++) {
                                        JsonObject jsonObj = resJSArray1.getJsonObject(i);
                                        Integer a = Integer.valueOf(jsonObj.getString("supply_chain_goods_id"));
                                        supplys.add(a);
                                    }

                                    //用于传递给构建sql的JsonObject
                                    queryJSObj.put("warehouse_id", warehouse_id);
                                    String supply_str="";
                                    Integer[] array = supplys.toArray(new Integer[supplys.size()]);
                                    for (int i = 0 ; i < array.length ; i ++){
                                        if (i==0){
                                            supply_str=supply_str+array[i];
                                        }else {
                                            supply_str=supply_str+","+array[i];
                                        }
                                    }
                                    queryJSObj.put("supply_chain_goods_id", supply_str);
                                    queryJSObj.put("supply_chain_goods_settle_package_spec", supply_chain_goods_settle_package_spec);
                                    queryJSObj.put("goods_batch_code", goods_batch_code);
                                    queryJSObj.put("is_delete", 0);
                                    queryJSObj.put("time_line", time_line);
                                    String sql = createSelectSqlForEndBatchDetial(queryJSObj, query_offset, query_limit);
                                    //获取满足条件的记录总数
                                    String count_sql = this.commonRepository.createCountSql(queryJSObj, "initial_batch_detail");
                                    Future<Integer> getTotalFuture = this.commonRepository.getRecordsCount(conn, count_sql, "getRecordsForEndBatchDetial");
                                    Future<JsonArray> future =  this.commonRepository.queryJsonArray(conn, sql, "getRecordsForEndBatchDetial");
                                    CompositeFuture.join(Arrays.asList(getTotalFuture, future )).onComplete(ar2 -> {
                                        if (ar2.succeeded()) {
                                            int total_records = Integer.valueOf(ar2.result().list().get(0).toString());
                                            JsonObject dataJSObj = new JsonObject();
                                            JsonArray recordsJSArray=(JsonArray)ar2.result().list().get(1);
                                            dataJSObj.put("total", total_records).put("records",recordsJSArray);
                                            responseJSObj.put("code", 200).put("msg", "获取记录成功").put("data", dataJSObj);
                                            promise.complete(responseJSObj);
                                        } else {
                                            logger.info("获取记录数据发生错误: {}", ar2.cause().getMessage());
                                            promise.complete(CommonUtils.createExceptionJsonObject(ar2, "getRecordsForEndBatchDetial", "获取期末库存批次明细失败"));
                                        }
                                    });
                                }
                            }).onFailure(err -> promise.complete(CommonUtils.createExceptionJsonObject(err, "getRecordsForEndBatchDetial", "获取期末库存批次明细失败")));
                }).
                onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getRecordsForEndBatchDetial", "数据库连接失败")));
        return promise.future();
    }


    /**
     * 创建查询符合搜索条件记录的sql语句——查看期末明细
     *
     * @param queryJS      body参数对象
     * @param query_offset 页面偏移
     * @param query_limit  页面记录数
     * @return 查询符合搜索条件的sql语句
     */
    public String createSelectSqlForEndBatchDetial(JsonObject queryJS, int query_offset, int query_limit) {
        SelectQuery sq = new SelectQuery();

        sq.addCustomColumns(new CustomSql("id"), new CustomSql("supply_chain_goods_id"),new CustomSql("time_line"),
                        new CustomSql("(select scg.supply_chain_goods_name from supply_chain_goods scg  where scg.is_delete=0 " +
                                "and scg.id= supply_chain_goods_id ) as supply_chain_goods_name "),new CustomSql("supply_chain_goods_settle_package_spec"), new CustomSql("goods_batch_code"),
                        new CustomSql("supplier_id"), new CustomSql("(select si.supplier_name from supplier_info si where si.is_delete=0 and si.id=supplier_id ) as supplier_name "),
                        new CustomSql("real_settlement_price"), new CustomSql("transport_price"), new CustomSql("land_price"), new CustomSql("inventory_land_acount"), new CustomSql("warehouse_id"),
                        new CustomSql("(select w.warehouse_name from warehouse w where w.is_delete=0 and w.id=warehouse_id ) as warehouse_name "),
                        new CustomSql("end_inventory_number"),new CustomSql("end_inventory_weight"),new CustomSql("end_inventory_amount"),
                        new CustomSql("is_delete"), new CustomSql("create_user"), new CustomSql("update_user"), new CustomSql("create_time"), new CustomSql("update_time"))
                .addCustomFromTable("initial_batch_detail");
        Iterator<String> iter = queryJS.fieldNames().iterator();
        while (iter.hasNext()) {
            String key = iter.next();
            try {
                String value = queryJS.getString(key);
                if (!value.equals("") && !value.equals("[]") && !value.equals("[all]")) {
                    String firstChar = value.substring(0, 1);
                    if (firstChar.equals("["))
                        value = value.substring(1, value.length() - 1);
                    value = value.replace(", ", "','");
                    // 添加where  InCondtion 子句
                    sq.getWhereClause().addCustomCondition(new InCondition(new CustomSql(key), value));
                }
            } catch (Exception e) {
                logger.info("createSql, 发生错误,错误原因: ", e.getMessage());
            }
        }
        return sq.validate() + " order by id desc limit " + query_limit + " offset " + query_offset;
    }


    /**
     * 查看加工报损单明细 根据传入的查询参数获取满足条件的记录
     *
     * @param client
     * @param config
     * @param bodyJSObj
     * @return
     */
    public Future<JsonObject> getRecordsForGoodsProcessLoss(PgPool client, JsonObject config, JsonObject bodyJSObj) {
        Promise<JsonObject> promise = Promise.promise();
        //最后返回的JsonObject对象
        JsonObject responseJSObj = new JsonObject();
        // 验证必须传的数值字段
        List<String> numeric_fields = Arrays.asList("warehouse_id", "aggregation_id", "page_size", "page_index");
        JsonObject numeric = this.commonRepository.verifyAllParamNumeric(bodyJSObj, numeric_fields);
        if (numeric != null) {
            promise.complete(numeric);
            return promise.future();
        }

        //从bodyJson中获取数据   各字段注释请参看数据库设计文档中数据表supplier_goods_process_loss的说明\
        String warehouse_id = bodyJSObj.getString("warehouse_id");
        String aggregation_id = bodyJSObj.getString("aggregation_id");
        String loss_date = bodyJSObj.getString("loss_date");

        int page_size = Integer.valueOf(bodyJSObj.getString("page_size"));
        int page_index = Integer.valueOf(bodyJSObj.getString("page_index"));
        int query_offset = (page_index - 1) * page_size;
        int query_limit = page_size;
        //用于传递给构建sql的JsonObject
        JsonObject queryJSObj = new JsonObject();


        queryJSObj.put("warehouse_id", warehouse_id);
        queryJSObj.put("aggregation_id", aggregation_id);
        queryJSObj.put("is_delete", 0);
        queryJSObj.put("loss_date", loss_date);
        this.commonRepository.getCon(client).onSuccess(conn -> {
            //先获取供应商商品加工报损单
            String sql = createSelectSqlForSupplierGoodsProcessLossDetail(queryJSObj, query_offset, query_limit);
            String sql2 = createSelectSqlForSupplierGoodsProcessLossDetail2(queryJSObj, query_offset, query_limit);
            //获取满足条件的记录总数
            String count_sql = this.commonRepository.createCountSql(queryJSObj, "supplier_goods_process_loss");
            String count_sql2 = this.commonRepository.createCountSql(queryJSObj, "sales_supplier_goods_process_loss");
            Future<Integer> getTotalFuture = this.commonRepository.getRecordsCount(conn, count_sql, "getRecordsForGoodsProcessLoss");
            Future<Integer> getTotalFuture2 = this.commonRepository.getRecordsCount(conn, count_sql2, "getRecordsForSalesGoodsProcessLoss");
            Future<JsonArray> future =  this.commonRepository.queryJsonArray(conn, sql, "getRecordsForGoodsProcessLoss");
            Future<JsonArray> future2 =  this.commonRepository.queryJsonArray(conn, sql2, "getRecordsForSalesGoodsProcessLoss");
            CompositeFuture.join(Arrays.asList(getTotalFuture, future ,getTotalFuture2,future2)).onComplete(ar2 -> {
                if (ar2.succeeded()) {
                    int total_records = Integer.valueOf(ar2.result().list().get(0).toString());
                    int total_records2 = Integer.valueOf(ar2.result().list().get(2).toString());
                    if (total_records!=0){
                        JsonObject dataJSObj = new JsonObject();
                        JsonArray recordsJSArray=(JsonArray)ar2.result().list().get(1);
                        dataJSObj.put("total", total_records).put("records",recordsJSArray);
                        responseJSObj.put("code", 200).put("msg", "获取记录成功").put("data", dataJSObj);
                        promise.complete(responseJSObj);
                    }else if (total_records2!=0){
                        JsonObject dataJSObj = new JsonObject();
                        JsonArray recordsJSArray=(JsonArray)ar2.result().list().get(3);
                        dataJSObj.put("total", total_records2).put("records",recordsJSArray);
                        responseJSObj.put("code", 200).put("msg", "获取记录成功").put("data", dataJSObj);
                        promise.complete(responseJSObj);
                    }else {
                        JsonObject dataJSObj = new JsonObject();
                        dataJSObj.put("total", 0).put("records","");
                        responseJSObj.put("code", 200).put("msg", "获取记录成功").put("data", dataJSObj);
                        promise.complete(responseJSObj);
                    }
                } else {
                    logger.info("获取记录数据发生错误: {}", ar2.cause().getMessage());
                    promise.complete(CommonUtils.createExceptionJsonObject(ar2, "getRecordsForGoodsProcessLoss", "获取供应商商品加工报损单失败"));
                }
            });
        }).onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getRecordsForGoodsProcessLoss", "数据库连接失败")));
        return promise.future();
    }

    /**
     * 创建查询符合搜索条件记录的sql语句——查看加工报损单明细
     *
     * @param queryJS      body参数对象
     * @param query_offset 页面偏移
     * @param query_limit  页面记录数
     * @return 查询符合搜索条件的sql语句
     */
    public String createSelectSqlForSupplierGoodsProcessLossDetail(JsonObject queryJS, int query_offset, int query_limit) {
        SelectQuery sq = new SelectQuery();
        sq.addCustomColumns(new CustomSql("id"), new CustomSql("warehouse_id"), new CustomSql("supplier_goods_process_loss_code"), new CustomSql("partner_loss_ratio_id"),
                        new CustomSql("(select plr.loss_type_name from partner_loss_ratio plr where plr.is_delete=0 " +
                                "and plr.id=partner_loss_ratio_id ) as loss_type_name"),
                        new CustomSql("(select spgiad.goods_batch_code " +
                                "from supplier_pickup_goods_inspect_accept_detail spgiad " +
                                "where spgiad.is_delete=0 " +
                                "and spgiad.id=supplier_pickup_goods_inspect_accept_detail_id ) as goods_batch_code "),
                        new CustomSql("(select w.warehouse_name from warehouse w where w.is_delete=0 and w.id=warehouse_id ) as warehouse_name "),
                        new CustomSql("(select scg.supply_chain_goods_name from supply_chain_goods scg  where scg.is_delete=0 " +
                                "and scg.id= supply_chain_goods_id ) as supply_chain_goods_name "), new CustomSql("supply_chain_goods_id"), new CustomSql("loss_weight"), new CustomSql("loss_number"),
                        new CustomSql("is_delete"), new CustomSql("create_user"), new CustomSql("update_user"), new CustomSql("create_time"), new CustomSql("update_time"))
                .addCustomFromTable("supplier_goods_process_loss");
        Iterator<String> iter = queryJS.fieldNames().iterator();
        while (iter.hasNext()) {
            String key = iter.next();
            try {
                String value = queryJS.getString(key);
                if (!value.equals("") && !value.equals("[]") && !value.equals("[all]")) {
                    String firstChar = value.substring(0, 1);
                    if (firstChar.equals("["))
                        value = value.substring(1, value.length() - 1);
                    value = value.replace(", ", "','");
                    // 添加where  InCondtion 子句
                    sq.getWhereClause().addCustomCondition(new InCondition(new CustomSql(key), value));
                }
            } catch (Exception e) {
                logger.info("createSql, 发生错误,错误原因: ", e.getMessage());
            }
        }
        return sq.validate() + " order by id desc limit " + query_limit + " offset " + query_offset;
    }

    /**
     * 创建查询符合搜索条件记录的sql语句——查看加工报损单明细
     *
     * @param queryJS      body参数对象
     * @param query_offset 页面偏移
     * @param query_limit  页面记录数
     * @return 查询符合搜索条件的sql语句
     */
    public String createSelectSqlForSupplierGoodsProcessLossDetail2(JsonObject queryJS, int query_offset, int query_limit) {
        SelectQuery sq = new SelectQuery();
        sq.addCustomColumns(new CustomSql("id"), new CustomSql("warehouse_id"), new CustomSql("sales_supplier_goods_process_loss_code"), new CustomSql("partner_loss_ratio_id"),
                        new CustomSql("(select plr.loss_type_name from partner_loss_ratio plr where plr.is_delete=0 and plr.id=partner_loss_ratio_id ) as loss_type_name"),
                        new CustomSql("(select spgiad.goods_batch_code " +
                                "from sales_supplier_pickup_goods_inspect_accept_detail spgiad " +
                                "where spgiad.is_delete=0 " +
                                "and spgiad.id=sales_supplier_pickup_goods_inspect_accept_detail_id ) as goods_batch_code "),
                        new CustomSql("(select w.warehouse_name from warehouse w where w.is_delete=0 and w.id=warehouse_id ) as warehouse_name "),
                        new CustomSql("(select scg.supply_chain_goods_name from supply_chain_goods scg  where scg.is_delete=0 " +
                                "and scg.id= supply_chain_goods_id ) as supply_chain_goods_name "), new CustomSql("supply_chain_goods_id"), new CustomSql("loss_weight"), new CustomSql("loss_number"),
                        new CustomSql("is_delete"), new CustomSql("create_user"), new CustomSql("update_user"), new CustomSql("create_time"), new CustomSql("update_time"))
                .addCustomFromTable("sales_supplier_goods_process_loss");
        Iterator<String> iter = queryJS.fieldNames().iterator();
        while (iter.hasNext()) {
            String key = iter.next();
            try {
                String value = queryJS.getString(key);
                if (!value.equals("") && !value.equals("[]") && !value.equals("[all]")) {
                    String firstChar = value.substring(0, 1);
                    if (firstChar.equals("["))
                        value = value.substring(1, value.length() - 1);
                    value = value.replace(", ", "','");
                    // 添加where  InCondtion 子句
                    sq.getWhereClause().addCustomCondition(new InCondition(new CustomSql(key), value));
                }
            } catch (Exception e) {
                logger.info("createSql, 发生错误,错误原因: ", e.getMessage());
            }
        }
        return sq.validate() + " order by id desc limit " + query_limit + " offset " + query_offset;
    }

    /**
     * 创建查询符合搜索条件记录的sql语句——查看销售地区供应商商品加工报损单明细
     *
     * @param queryJS      body参数对象
     * @param query_offset 页面偏移
     * @param query_limit  页面记录数
     * @return 查询符合搜索条件的sql语句
     */
    public String createSelectSqlForSalesSupplierGoodsProcessLossDetail(JsonObject queryJS, int query_offset, int query_limit) {
        SelectQuery sq = new SelectQuery();
        sq.addCustomColumns(new CustomSql("id"), new CustomSql("warehouse_id"), new CustomSql("supplier_goods_process_loss_code"), new CustomSql("loss_type_name"),
        new CustomSql("goods_batch_code"), new CustomSql("supply_chain_goods_id"), new CustomSql("loss_weight"), new CustomSql("loss_number"),
        new CustomSql("is_delete"), new CustomSql("create_user"), new CustomSql("update_user"), new CustomSql("create_time"), new CustomSql("update_time"))
        .addCustomFromTable("sales_supplier_goods_process_loss");
        Iterator<String> iter = queryJS.fieldNames().iterator();
        while (iter.hasNext()) {
            String key = iter.next();
            try {
                String value = queryJS.getString(key);
                if (!value.equals("") && !value.equals("[]") && !value.equals("[all]")) {
                    String firstChar = value.substring(0, 1);
                    if (firstChar.equals("["))
                        value = value.substring(1, value.length() - 1);
                    value = value.replace(", ", "','");
                    // 添加where  InCondtion 子句
                    sq.getWhereClause().addCustomCondition(new InCondition(new CustomSql(key), value));
                }
            } catch (Exception e) {
                logger.info("createSql, 发生错误,错误原因: ", e.getMessage());
            }
        }
        return sq.validate() + " order by id desc limit " + query_limit + " offset " + query_offset;
    }


    /**
     * 查看盘盈盘亏单明细 根据传入的查询参数获取满足条件的记录
     *
     * @param client
     * @param config
     * @param bodyJSObj
     * @return
     */
    public Future<JsonObject> getRecordsForGoodsProfitLoss(PgPool client, JsonObject config, JsonObject bodyJSObj) {
        Promise<JsonObject> promise = Promise.promise();
        //最后返回的JsonObject对象
        JsonObject responseJSObj = new JsonObject();
        // 验证必须传的数值字段
        List<String> numeric_fields = Arrays.asList("warehouse_id", "aggregation_id", "page_size", "page_index");
        JsonObject numeric = this.commonRepository.verifyAllParamNumeric(bodyJSObj, numeric_fields);
        if (numeric != null) {
            promise.complete(numeric);
            return promise.future();
        }

        //从bodyJson中获取数据   各字段注释请参看数据库设计文档中数据表supplier_goods_profit_loss的说明\
        String warehouse_id = bodyJSObj.getString("warehouse_id");
        String aggregation_id = bodyJSObj.getString("aggregation_id");
        String inspect_stock_date = bodyJSObj.getString("inspect_stock_date");

        int page_size = Integer.valueOf(bodyJSObj.getString("page_size"));
        int page_index = Integer.valueOf(bodyJSObj.getString("page_index"));
        int query_offset = (page_index - 1) * page_size;
        int query_limit = page_size;
        //用于传递给构建sql的JsonObject
        JsonObject queryJSObj = new JsonObject();

        //定义多个set，存储每一个 initial_batch_detail 中多个code
        Set<String> warehouse_id_list_set = new HashSet<String>();
        Set<String> supply_chain_goods_id_list_set = new HashSet<String>();

        queryJSObj.put("warehouse_id", warehouse_id);
        queryJSObj.put("aggregation_id", aggregation_id);
        queryJSObj.put("is_delete", 0);
        queryJSObj.put("inspect_stock_date", inspect_stock_date);
        this.commonRepository.getCon(client).onSuccess(conn -> {
            //先获取供应商商品盘盈盘亏单
            String sql = createSelectSqlForSupplierGoodsProfitLossDetail(queryJSObj, query_offset, query_limit);
            String sql2 = createSelectSqlForSalesSupplierGoodsProfitLossDetail(queryJSObj, query_offset, query_limit);
            //获取满足条件的记录总数
            String count_sql = this.commonRepository.createCountSql(queryJSObj, "supplier_goods_profit_loss");
            String count_sql2 = this.commonRepository.createCountSql(queryJSObj, "sales_supplier_goods_profit_loss");
            Future<Integer> getTotalFuture = this.commonRepository.getRecordsCount(conn, count_sql, "getRecordsForGoodsProfitLoss");
            Future<Integer> getTotalFuture2 = this.commonRepository.getRecordsCount(conn, count_sql2, "getRecordsForSalesGoodsProfitLoss");
            Future<JsonArray> future =  this.commonRepository.queryJsonArray(conn, sql, "getRecordsForGoodsProfitLoss");
            Future<JsonArray> future2 =  this.commonRepository.queryJsonArray(conn, sql2, "getRecordsForSalesGoodsProfitLoss");
            CompositeFuture.join(Arrays.asList(getTotalFuture, future ,getTotalFuture2,future2)).onComplete(ar2 -> {
                if (ar2.succeeded()) {
                    int total_records = Integer.valueOf(ar2.result().list().get(0).toString());
                    int total_records2 = Integer.valueOf(ar2.result().list().get(2).toString());
                    if (total_records!=0){
                        JsonObject dataJSObj = new JsonObject();
                        JsonArray recordsJSArray=(JsonArray)ar2.result().list().get(1);
                        dataJSObj.put("total", total_records).put("records",recordsJSArray);
                        responseJSObj.put("code", 200).put("msg", "获取记录成功").put("data", dataJSObj);
                        promise.complete(responseJSObj);
                    }else if (total_records2!=0){
                        JsonObject dataJSObj = new JsonObject();
                        JsonArray recordsJSArray=(JsonArray)ar2.result().list().get(3);
                        dataJSObj.put("total", total_records2).put("records",recordsJSArray);
                        responseJSObj.put("code", 200).put("msg", "获取记录成功").put("data", dataJSObj);
                        promise.complete(responseJSObj);
                    }else {
                        JsonObject dataJSObj = new JsonObject();
                        dataJSObj.put("total", 0).put("records","");
                        responseJSObj.put("code", 200).put("msg", "获取记录成功").put("data", dataJSObj);
                        promise.complete(responseJSObj);
                    }
                } else {
                    logger.info("获取记录数据发生错误: {}", ar2.cause().getMessage());
                    promise.complete(CommonUtils.createExceptionJsonObject(ar2, "getRecordsForGoodsProfitLoss", "获取盘盈盘亏单明细失败"));
                }
            });


        }).onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getRecordsForGoodsProfitLoss", "数据库连接失败")));
        return promise.future();
    }

    /**
     * 创建查询符合搜索条件记录的sql语句——查看盘盈盘亏单明细
     *
     * @param queryJS      body参数对象
     * @param query_offset 页面偏移
     * @param query_limit  页面记录数
     * @return 查询符合搜索条件的sql语句
     */
    public String createSelectSqlForSupplierGoodsProfitLossDetail(JsonObject queryJS, int query_offset, int query_limit) {
        SelectQuery sq = new SelectQuery();
        sq.addCustomColumns(new CustomSql("id"), new CustomSql("supplier_goods_profit_loss_code"), new CustomSql("warehouse_id"),
                        new CustomSql("(select w.warehouse_name from warehouse w where w.is_delete=0 and w.id=warehouse_id ) as warehouse_name "),
                        new CustomSql("(select scg.supply_chain_goods_name from supply_chain_goods scg  where scg.is_delete=0 " +
                                "and scg.id= supply_chain_goods_id ) as supply_chain_goods_name "), new CustomSql("supply_chain_goods_id"), new CustomSql("(select spppd.real_settlement_package_spec " +
                                "from supplier_pickup_plan_payment_detail spppd " +
                                "where spppd.is_delete=0 " +
                                "and spppd.id =( " +
                                "    select spgiad.supplier_pickup_plan_payment_detail_id " +
                                "    from supplier_pickup_goods_inspect_accept_detail spgiad " +
                                "    where spgiad.is_delete=0 " +
                                "      and spgiad.id =supplier_pickup_goods_inspect_accept_detail_id " +
                                "    ) )as real_settlement_package_spec"), new CustomSql("profit_loss_number"),new CustomSql("profit_loss_weight"),
                        new CustomSql("(select spgiad.goods_batch_code " +
                                "from supplier_pickup_goods_inspect_accept_detail spgiad " +
                                "where spgiad.is_delete=0 " +
                                "and spgiad.id=supplier_pickup_goods_inspect_accept_detail_id ) as goods_batch_code "),
                        new CustomSql("is_delete"), new CustomSql("create_user"), new CustomSql("update_user"), new CustomSql("create_time"), new CustomSql("update_time"))
                .addCustomFromTable("supplier_goods_profit_loss");
        Iterator<String> iter = queryJS.fieldNames().iterator();
        while (iter.hasNext()) {
            String key = iter.next();
            try {
                String value = queryJS.getString(key);
                if (!value.equals("") && !value.equals("[]") && !value.equals("[all]")) {
                    String firstChar = value.substring(0, 1);
                    if (firstChar.equals("["))
                        value = value.substring(1, value.length() - 1);
                    value = value.replace(", ", "','");
                    // 添加where  InCondtion 子句
                    sq.getWhereClause().addCustomCondition(new InCondition(new CustomSql(key), value));
                }
            } catch (Exception e) {
                logger.info("createSql, 发生错误,错误原因: ", e.getMessage());
            }
        }
        return sq.validate() + " order by id desc limit " + query_limit + " offset " + query_offset;
    }

    /**
     * 创建查询符合搜索条件记录的sql语句——查看销售地区供应商商品盘盈盘亏单明细
     *
     * @param queryJS      body参数对象
     * @param query_offset 页面偏移
     * @param query_limit  页面记录数
     * @return 查询符合搜索条件的sql语句
     */
    public String createSelectSqlForSalesSupplierGoodsProfitLossDetail(JsonObject queryJS, int query_offset, int query_limit) {
        SelectQuery sq = new SelectQuery();
        sq.addCustomColumns(new CustomSql("id"), new CustomSql("supplier_goods_profit_loss_code"), new CustomSql("warehouse_id"),
                        new CustomSql("(select w.warehouse_name from warehouse w where w.is_delete=0 and w.id=warehouse_id ) as warehouse_name "),
                        new CustomSql("(select scg.supply_chain_goods_name from supply_chain_goods scg  where scg.is_delete=0 " +
                                "and scg.id= supply_chain_goods_id ) as supply_chain_goods_name "), new CustomSql("supply_chain_goods_id"),
                        new CustomSql("(select spppd.real_settlement_package_spec " +
                                "from sales_pickup_plan_command spppd " +
                                "where spppd.is_delete=0 " +
                                "  and spppd.id =( " +
                                "    select spgiad.sales_pickup_plan_command_id " +
                                "    from sales_supplier_pickup_goods_inspect_accept_detail spgiad " +
                                "    where spgiad.is_delete=0 " +
                                "      and spgiad.id =sales_supplier_pickup_goods_inspect_accept_detail_id " +
                                ")) as real_settlement_package_spec"), new CustomSql("profit_loss_number"),new CustomSql("profit_loss_weight"),
                        new CustomSql("(select spgiad.goods_batch_code " +
                                "from sales_supplier_pickup_goods_inspect_accept_detail spgiad " +
                                "where spgiad.is_delete=0 " +
                                "and spgiad.id=sales_supplier_pickup_goods_inspect_accept_detail_id ) as goods_batch_code "),
                        new CustomSql("is_delete"), new CustomSql("create_user"), new CustomSql("update_user"), new CustomSql("create_time"), new CustomSql("update_time"))
                .addCustomFromTable("sales_supplier_goods_profit_loss");
        Iterator<String> iter = queryJS.fieldNames().iterator();
        while (iter.hasNext()) {
            String key = iter.next();
            try {
                String value = queryJS.getString(key);
                if (!value.equals("") && !value.equals("[]") && !value.equals("[all]")) {
                    String firstChar = value.substring(0, 1);
                    if (firstChar.equals("["))
                        value = value.substring(1, value.length() - 1);
                    value = value.replace(", ", "','");
                    // 添加where  InCondtion 子句
                    sq.getWhereClause().addCustomCondition(new InCondition(new CustomSql(key), value));
                }
            } catch (Exception e) {
                logger.info("createSql, 发生错误,错误原因: ", e.getMessage());
            }
        }
        return sq.validate() + " order by id desc limit " + query_limit + " offset " + query_offset;
    }







    /**
     * 根据传入的查询参数获取满足条件的记录——查看商品折损供应商补贴单明细
     *
     * @param client    连接池对象
     * @param config    配置对象
     * @param bodyJSObj body参数对象
     * @return 满足条件的记录
     */
    public Future<JsonObject> getRecordsForGoodDamageSupplierSubsidyDetail(PgPool client, JsonObject config, JsonObject bodyJSObj) {
        Promise<JsonObject> promise = Promise.promise();
        // 最后返回的JsonObject对象
        JsonObject responseJSObj = new JsonObject();
        //从bodyJson中获取传递的参数, 各参数字段含义 请参看 insertOrUpdate方法体中的注释
        /** 从bodyJson中获取传递的参数说明,适用于方法createSelectSql 和 getRecords
         */

        // 验证必须传的数值字段
        List<String> numeric_fields = Arrays.asList("warehouse_id", "supply_chain_goods_settle_package_spec", "page_size", "page_index","aggregation_id");
        JsonObject numeric = this.commonRepository.verifyAllParamNumeric(bodyJSObj, numeric_fields);
        if (numeric != null) {
            promise.complete(numeric);
            return promise.future();
        }

        //从bodyJson中获取数据   各字段注释请参看数据库设计文档中数据表sales_supplier_goods_profit_loss的说明\
        String warehouse_id = bodyJSObj.getString("warehouse_id");
        String aggregation_id = bodyJSObj.getString("aggregation_id");
        String supply_chain_goods_settle_package_spec = bodyJSObj.getString("supply_chain_goods_settle_package_spec");
        String goods_batch_code = bodyJSObj.getString("goods_batch_code");
        String generate_date = bodyJSObj.getString("generate_date");

        int page_size = Integer.valueOf(bodyJSObj.getString("page_size"));
        int page_index = Integer.valueOf(bodyJSObj.getString("page_index"));
        int query_offset = (page_index - 1) * page_size;
        int query_limit = page_size;
        // 传递给构建查询sql中where子句的JsonObject,
        JsonObject queryJSObj = new JsonObject();
        queryJSObj.put("warehouse_id", warehouse_id);
        bodyJSObj.put("aggregation_id",aggregation_id);
        queryJSObj.put("real_supply_chain_goods_settle_package_spec", supply_chain_goods_settle_package_spec);
        queryJSObj.put("goods_batch_code", goods_batch_code);
        queryJSObj.put("is_delete", 0);
        queryJSObj.put("generate_date", generate_date);
        this.commonRepository.getCon(client).onSuccess(conn -> {
                    //获取满足条件的记录总数
                    String count_sql = this.commonRepository.createCountSql(queryJSObj, "good_damage_supplier_subsidy_detail");
                    Future<Integer> getTotalFuture = this.commonRepository.getRecordsCount(conn, count_sql, "get_good_damage_supplier_subsidy_detail");
                    String sql = createSelectSqlForGoodDamageSupplierSubsidyDetail(queryJSObj, query_offset, query_limit);
                    Future<JsonArray> future =  this.commonRepository.queryJsonArray(conn, sql, "getRecordsForGoodDamageSupplierSubsidyDetail");

                    CompositeFuture.join(Arrays.asList(getTotalFuture,future)).onComplete(ar2 -> {
                        if (ar2.succeeded()) {
                            int total_records = Integer.valueOf(ar2.result().list().get(0).toString());
                            JsonObject dataJSObj = new JsonObject();
                            JsonArray recordsJSArray=(JsonArray)ar2.result().list().get(1);
                            dataJSObj.put("total", total_records).put("records",recordsJSArray);
                            responseJSObj.put("code", 200).put("msg", "获取记录成功").put("data", dataJSObj);
                            promise.complete(responseJSObj);
                        } else {
                            logger.info("获取记录数据发生错误: {}", ar2.cause().getMessage());
                            promise.complete(CommonUtils.createExceptionJsonObject(ar2, "getGoodDamageSupplierSubsidyDetailRecords", "获取商品折损供应商补贴单明细失败"));
                        }
                    });
        }).onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getGoodDamageSupplierSubsidyDetailRecords", "数据库连接失败")));
        return promise.future();
    }

    /**
     * 创建查询符合搜索条件记录的sql语句  查看商品折损供应商补贴单明细
     *
     * @param queryJS      body参数对象
     * @param query_offset 页面偏移
     * @param query_limit  页面记录数
     * @return 查询符合搜索条件的sql语句
     */
    public String createSelectSqlForGoodDamageSupplierSubsidyDetail(JsonObject queryJS, int query_offset, int query_limit) {
        SelectQuery sq = new SelectQuery();
        sq.addCustomColumns(new CustomSql("id"), new CustomSql("site_id"), new CustomSql("(select site_name from site where is_delete=0 and id=site_id ) as site_name "),
        new CustomSql("warehouse_id"), new CustomSql("(select w.warehouse_name from warehouse w where w.is_delete=0 and w.id=warehouse_id ) as warehouse_name "),
        new CustomSql("goods_gather_area_id"), new CustomSql("(select gga.goods_gather_area_name from goods_gather_area gga  where gga.is_delete=0 and gga.id=goods_gather_area_id ) as goods_gather_area_name "),
        new CustomSql("supplier_id"), new CustomSql("(select si.supplier_name from supplier_info si where si.is_delete=0 and si.id=supplier_id ) as supplier_name "),
        new CustomSql("good_damage_supplier_subsidy_code"), new CustomSql("generate_date"), new CustomSql("aggregation_id"),
        new CustomSql("(select ag.aggregation_name from aggregation_goods ag where ag.is_delete=0 and ag.id=aggregation_id ) as aggregation_name"),
        new CustomSql("supply_chain_goods_id"), new CustomSql("(select scg.supply_chain_goods_name from supply_chain_goods scg where scg.is_delete=0 and scg.id=supply_chain_goods_id ) as supply_chain_goods_name "),
        new CustomSql("goods_batch_code"), new CustomSql("real_supply_chain_goods_grade_id"),new CustomSql("( select gg.grade_name " +
                                "from goods_grade gg " +
                                "where gg.is_delete=0 " +
                                "and gg.id=real_supply_chain_goods_grade_id ) as grade_name"),
        new CustomSql("(select sg.grade_name from supplier_grade sg where sg.is_delete=0 and sg.id=real_supply_chain_goods_grade_id ) as grade_name "),
        new CustomSql("real_supply_chain_goods_settle_package_spec"), new CustomSql("real_deal_price"), new CustomSql("supplier_pickup_goods_inspect_accept_code"), new CustomSql("retrun_warehouse_number"), new CustomSql("retrun_warehouse_weight"),
        new CustomSql("damage_number"), new CustomSql("damage_weight"), new CustomSql("good_damage_supplier_subsidy_price"),
        new CustomSql("is_delete"), new CustomSql("create_user"), new CustomSql("update_user"), new CustomSql("create_time"), new CustomSql("update_time"))
        .addCustomFromTable("good_damage_supplier_subsidy_detail");
        Iterator<String> iter = queryJS.fieldNames().iterator();
        while (iter.hasNext()) {
            String key = iter.next();
            try {
                String value = queryJS.getString(key);
                if (!value.equals("") && !value.equals("[]") && !value.equals("[all]")) {
                    String firstChar = value.substring(0, 1);
                    if (firstChar.equals("["))
                        value = value.substring(1, value.length() - 1);
                    value = value.replace(", ", "','");
                    // 添加where  InCondtion 子句
                    sq.getWhereClause().addCustomCondition(new InCondition(new CustomSql(key), value));
                }
            } catch (Exception e) {
                logger.info("createSql, 发生错误,错误原因: ", e.getMessage());
            }
        }
        return sq.validate() + " order by id desc limit " + query_limit + " offset " + query_offset;
    }


    /**
     * 根据传入的查询参数获取满足条件的记录——查看商品内部赔偿单明细
     *
     * @param client    连接池对象
     * @param config    配置对象
     * @param bodyJSObj body参数对象
     * @return 满足条件的记录
     */
    public Future<JsonObject> getRecordsForGoodIndoorSubsidyDetail(PgPool client, JsonObject config, JsonObject bodyJSObj) {
        Promise<JsonObject> promise = Promise.promise();
        // 最后返回的JsonObject对象
        JsonObject responseJSObj = new JsonObject();
        //从bodyJson中获取传递的参数, 各参数字段含义 请参看 insertOrUpdate方法体中的注释
        /** 从bodyJson中获取传递的参数说明,适用于方法createSelectSql 和 getRecords
         */

        // 验证必须传的数值字段
        List<String> numeric_fields = Arrays.asList("warehouse_id", "aggregation_id","supply_chain_goods_settle_package_spec", "page_size", "page_index");
        JsonObject numeric = this.commonRepository.verifyAllParamNumeric(bodyJSObj, numeric_fields);
        if (numeric != null) {
            promise.complete(numeric);
            return promise.future();
        }

        //从bodyJson中获取数据   各字段注释请参看数据库设计文档中数据表sales_supplier_goods_profit_loss的说明\
        String warehouse_id = bodyJSObj.getString("warehouse_id");
        String aggregation_id = bodyJSObj.getString("aggregation_id");
        String supply_chain_goods_settle_package_spec = bodyJSObj.getString("supply_chain_goods_settle_package_spec");
        String goods_batch_code = bodyJSObj.getString("goods_batch_code");
        String generate_date = bodyJSObj.getString("generate_date");

        int page_size = Integer.valueOf(bodyJSObj.getString("page_size"));
        int page_index = Integer.valueOf(bodyJSObj.getString("page_index"));
        int query_offset = (page_index - 1) * page_size;
        int query_limit = page_size;
        // 传递给构建查询sql中where子句的JsonObject,
        JsonObject queryJSObj = new JsonObject();
        queryJSObj.put("warehouse_id", warehouse_id);
        queryJSObj.put("aggregation_id", aggregation_id);
        queryJSObj.put("real_supply_chain_goods_settle_package_spec", supply_chain_goods_settle_package_spec);
        queryJSObj.put("goods_batch_code", goods_batch_code);
        queryJSObj.put("generate_date", generate_date);
        queryJSObj.put("is_delete", 0);
        this.commonRepository.getCon(client).onSuccess(conn -> {
                    //获取满足条件的记录总数
                    String count_sql = this.commonRepository.createCountSql(queryJSObj, "good_indoor_subsidy_detail");
                    Future<Integer> getTotalFuture = this.commonRepository.getRecordsCount(conn, count_sql, "getRecordsForGoodIndoorSubsidyDetail");
                    String sql = createSelectSqlForGoodIndoorSubsidyDetail(queryJSObj, query_offset, query_limit);
                    Future<JsonArray> future =  this.commonRepository.queryJsonArray(conn, sql, "getRecordsForGoodIndoorSubsidyDetail");
                    CompositeFuture.join(Arrays.asList(getTotalFuture,  future)).onComplete(ar2 -> {
                        if (ar2.succeeded()) {
                            int total_records = Integer.valueOf(ar2.result().list().get(0).toString());
                            JsonObject dataJSObj = new JsonObject();
                            JsonArray recordsJSArray=(JsonArray)ar2.result().list().get(1);
                            dataJSObj.put("total", total_records).put("records",recordsJSArray);
                            responseJSObj.put("code", 200).put("msg", "获取记录成功").put("data", dataJSObj);
                            promise.complete(responseJSObj);
                        } else {
                            logger.info("获取记录数据发生错误: {}", ar2.cause().getMessage());
                            promise.complete(CommonUtils.createExceptionJsonObject(ar2, "getRecordsForGoodIndoorSubsidyDetail", "获取商品内部赔偿单明细失败"));
                        }
                    });
        }).onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getRecordsForGoodIndoorSubsidyDetail", "数据库连接失败")));
        return promise.future();
    }

    /**
     * 创建查询符合搜索条件记录的sql语句  查看商品内部赔偿单明细
     *
     * @param queryJS      body参数对象
     * @param query_offset 页面偏移
     * @param query_limit  页面记录数
     * @return 查询符合搜索条件的sql语句
     */
    public String createSelectSqlForGoodIndoorSubsidyDetail(JsonObject queryJS, int query_offset, int query_limit) {
        SelectQuery sq = new SelectQuery();
        sq.addCustomColumns(new CustomSql("id"), new CustomSql("site_id"),new CustomSql("(select site_name from site where is_delete=0 and id=site_id ) as site_name "),
                        new CustomSql("warehouse_id"),new CustomSql("(select w.warehouse_name from warehouse w where w.is_delete=0 and w.id=warehouse_id ) as warehouse_name "),
        new CustomSql("good_indoor_subsidy_code"), new CustomSql("generate_date"), new CustomSql("aggregation_id"),
                        new CustomSql("(select ag.aggregation_name from aggregation_goods ag where ag.is_delete=0 and ag.id=aggregation_id ) as aggregation_name"), new CustomSql("supply_chain_goods_id"),
                        new CustomSql("(select scg.supply_chain_goods_name from supply_chain_goods scg where scg.is_delete=0 and scg.id=supply_chain_goods_id ) as supply_chain_goods_name "),
                        new CustomSql("goods_batch_code"),
        new CustomSql("real_supply_chain_goods_grade_id"),new CustomSql("(select sg.grade_name from goods_grade sg where sg.is_delete=0 and sg.id=real_supply_chain_goods_grade_id ) as grade_name "),
                        new CustomSql("real_supply_chain_goods_settle_package_spec"), new CustomSql("indoor_subsidy_number"), new CustomSql("indoor_subsidy_weight"),
        new CustomSql("indoor_subsidy_price"), new CustomSql("land_price"),
        new CustomSql("is_delete"), new CustomSql("create_user"), new CustomSql("update_user"), new CustomSql("create_time"), new CustomSql("update_time"))
        .addCustomFromTable("good_indoor_subsidy_detail");
        Iterator<String> iter = queryJS.fieldNames().iterator();
        while (iter.hasNext()) {
            String key = iter.next();
            try {
                String value = queryJS.getString(key);
                if (!value.equals("") && !value.equals("[]") && !value.equals("[all]")) {
                    String firstChar = value.substring(0, 1);
                    if (firstChar.equals("["))
                        value = value.substring(1, value.length() - 1);
                    value = value.replace(", ", "','");
                    // 添加where  InCondtion 子句
                    sq.getWhereClause().addCustomCondition(new InCondition(new CustomSql(key), value));
                }
            } catch (Exception e) {
                logger.info("createSql, 发生错误,错误原因: ", e.getMessage());
            }
        }
        return sq.validate() + " order by id desc limit " + query_limit + " offset " + query_offset;
    }


    /**
     * 根据传入的查询参数获取满足条件的记录——查看销售投诉赔偿单明细
     *
     * @param client    连接池对象
     * @param config    配置对象
     * @param bodyJSObj body参数对象
     * @return 满足条件的记录
     */
    public Future<JsonObject> getRecordsForSalesComplaintsSubsidyDetail(PgPool client, JsonObject config, JsonObject bodyJSObj) {
        Promise<JsonObject> promise = Promise.promise();
        // 最后返回的JsonObject对象
        JsonObject responseJSObj = new JsonObject();
        //从bodyJson中获取传递的参数, 各参数字段含义 请参看 insertOrUpdate方法体中的注释
        /** 从bodyJson中获取传递的参数说明,适用于方法createSelectSql 和 getRecords
         */

        // 验证必须传的数值字段
        List<String> numeric_fields = Arrays.asList("aggregation_id", "warehouse_id","supply_chain_goods_settle_package_spec", "page_size", "page_index");
        JsonObject numeric = this.commonRepository.verifyAllParamNumeric(bodyJSObj, numeric_fields);
        if (numeric != null) {
            promise.complete(numeric);
            return promise.future();
        }

        //从bodyJson中获取数据   各字段注释请参看数据库设计文档中数据表sales_supplier_goods_profit_loss的说明\
        String warehouse_id = bodyJSObj.getString("warehouse_id");
        String aggregation_id = bodyJSObj.getString("aggregation_id");
        String supply_chain_goods_settle_package_spec = bodyJSObj.getString("supply_chain_goods_settle_package_spec");
        String goods_batch_code = bodyJSObj.getString("goods_batch_code");
        String trade_date = bodyJSObj.getString("trade_date");

        int page_size = Integer.valueOf(bodyJSObj.getString("page_size"));
        int page_index = Integer.valueOf(bodyJSObj.getString("page_index"));
        int query_offset = (page_index - 1) * page_size;
        int query_limit = page_size;
        //首先根据聚合商品名获取对应的供应链商品名
        Set<Integer> supplys= new HashSet<>();
        this.commonRepository.getCon(client).onSuccess(conn -> {
            //获取满足条件的记录总数
            JsonObject queryJSObj1 = new JsonObject();
            queryJSObj1.put("aggregation_id", aggregation_id);
            String get_supply_chain_gooods_id_sql = createSelectSqlGetSupplyChainGooodsId(queryJSObj1, query_offset, query_limit);
            this.commonRepository.queryJsonArray(conn, get_supply_chain_gooods_id_sql, "getInitialBatchDetialRecords")
                    .onSuccess(resJSArray1 -> {
                        //        取出每条记录中含code字段的值放入对应的集合
                        if (resJSArray1.size()==0){
                            JsonObject dataJSObj = new JsonObject();
                            dataJSObj.put("total", 0).put("records","");
                            responseJSObj.put("code", 200).put("msg", "获取记录成功").put("data", dataJSObj);
                            promise.complete(responseJSObj);
                        }else {
                            for (int i = 0; i < resJSArray1.size(); i++) {
                                JsonObject jsonObj = resJSArray1.getJsonObject(i);
                                Integer a = Integer.valueOf(jsonObj.getString("supply_chain_goods_id"));
                                supplys.add(a);
                            }
                            // 传递给构建查询sql中where子句的JsonObject,
                            JsonObject queryJSObj = new JsonObject();
                            queryJSObj.put("warehouse_id", warehouse_id);
                            String supply_str="";
                            Integer[] array = supplys.toArray(new Integer[supplys.size()]);
                            for (int i = 0 ; i < array.length ; i ++){
                                if (i==0){
                                    supply_str=supply_str+array[i];
                                }else {
                                    supply_str=supply_str+","+array[i];
                                }
                            }
                            queryJSObj.put("supply_chain_goods_id",supply_str);
                            queryJSObj.put("supply_chain_goods_settle_package_spec", supply_chain_goods_settle_package_spec);
                            queryJSObj.put("goods_batch_code", goods_batch_code);
                            queryJSObj.put("trade_date", trade_date);
                            queryJSObj.put("is_delete", 0);
                            String count_sql = this.commonRepository.createCountSql(queryJSObj, "sales_complaints_subsidy_detail");
                            Future<Integer> getTotalFuture = this.commonRepository.getRecordsCount(conn, count_sql, "getRecordsForSalesComplaintsSubsidyDetail");
                            String sql = createSelectSqlForSalesComplaintsSubsidyDetail(queryJSObj, query_offset, query_limit);
                            Future<JsonArray> future =  this.commonRepository.queryJsonArray(conn, sql, "getRecordsForSalesComplaintsSubsidyDetail");
                            CompositeFuture.join(Arrays.asList(getTotalFuture, future)).onComplete(ar2 -> {
                                if (ar2.succeeded()) {
                                    int total_records = Integer.valueOf(ar2.result().list().get(0).toString());
                                    JsonObject dataJSObj = new JsonObject();
                                    JsonArray recordsJSArray=(JsonArray)ar2.result().list().get(1);
                                    dataJSObj.put("total", total_records).put("records",recordsJSArray);
                                    responseJSObj.put("code", 200).put("msg", "获取记录成功").put("data", dataJSObj);
                                    promise.complete(responseJSObj);
                                } else {
                                    logger.info("获取记录数据发生错误: {}", ar2.cause().getMessage());
                                    promise.complete(CommonUtils.createExceptionJsonObject(ar2, "getRecordsForSalesComplaintsSubsidyDetail", "获取销售投诉赔偿单明细失败"));
                                }
                            });

                        }


                    }).onFailure(err -> promise.complete(CommonUtils.createExceptionJsonObject(err, "getRecordsForSalesReturnWarehouseDetial", "获取供应链商品参数失败")));
        }).onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getRecordsForSalesComplaintsSubsidyDetail", "数据库连接失败")));
        return promise.future();
    }

    /**
     * 创建查询符合搜索条件记录的sql语句  查看销售投诉赔偿单明细
     *
     * @param queryJS      body参数对象
     * @param query_offset 页面偏移
     * @param query_limit  页面记录数
     * @return 查询符合搜索条件的sql语句
     */
    public String createSelectSqlForSalesComplaintsSubsidyDetail(JsonObject queryJS, int query_offset, int query_limit) {
        SelectQuery sq = new SelectQuery();
        sq.addCustomColumns(new CustomSql("id"), new CustomSql("trade_date"), new CustomSql("warehouse_id"),new CustomSql("(select w.warehouse_name from warehouse w where w.is_delete=0 and w.id=warehouse_id ) as warehouse_name "),
        new CustomSql("sales_complaints_subsidy_code"), new CustomSql("buyer_name"), new CustomSql("buyer_id"), new CustomSql("order_code"), new CustomSql("list_goods_id"),new CustomSql("(select lg.list_goods_name from list_goods lg where lg.is_delete=0 and lg.id=list_goods_id ) as list_goods_name "),
        new CustomSql("list_package_spec"), new CustomSql("sales_package_spec_id"),new CustomSql("(select spst.package_spec_name from sales_package_spec spst " +
        " where spst.is_delete=0 and spst.id=sales_package_spec_id ) type_name "), new CustomSql("supply_chain_goods_id"), new CustomSql("(select scg.supply_chain_goods_name from supply_chain_goods scg " +
        " where scg.is_delete=0  and scg.id=supply_chain_goods_id) as supply_chain_goods_name "),
         new CustomSql("supply_chain_goods_settle_package_spec"),
        new CustomSql("order_number"), new CustomSql("order_weight"), new CustomSql("delivery_date"), new CustomSql("shift_delivery_plan_completion_code"), new CustomSql("goods_batch_code"), new CustomSql("sales_complaints_subsidy_describe"),
        new CustomSql("sales_complaints_subsidy_price"), new CustomSql("site_id"),new CustomSql("(select site_name from site where is_delete=0 and id=site_id ) as site_name "),
        new CustomSql("is_delete"), new CustomSql("create_user"), new CustomSql("update_user"), new CustomSql("create_time"), new CustomSql("update_time"))
        .addCustomFromTable("sales_complaints_subsidy_detail");
        Iterator<String> iter = queryJS.fieldNames().iterator();
        while (iter.hasNext()) {
            String key = iter.next();
            try {
                String value = queryJS.getString(key);
                if (!value.equals("") && !value.equals("[]") && !value.equals("[all]")) {
                    String firstChar = value.substring(0, 1);
                    if (firstChar.equals("["))
                        value = value.substring(1, value.length() - 1);
                    value = value.replace(", ", "','");
                    // 添加where  InCondtion 子句
                    sq.getWhereClause().addCustomCondition(new InCondition(new CustomSql(key), value));
                }
            } catch (Exception e) {
                logger.info("createSql, 发生错误,错误原因: ", e.getMessage());
            }
        }
        return sq.validate() + " order by id desc limit " + query_limit + " offset " + query_offset;
    }

    private String createSelectSqlGetSupplyChainGooodsId(JsonObject queryJS, int query_offset, int query_limit) {
        SelectQuery sq = new SelectQuery();
        sq.addCustomColumns(new CustomSql("supply_chain_goods_id")).addCustomFromTable("goods_parameter");
        Iterator<String> iter = queryJS.fieldNames().iterator();
        while (iter.hasNext()) {
            String key = iter.next();
            try {
                String value = queryJS.getString(key);
                if (!value.equals("") && !value.equals("[]") && !value.equals("[all]")) {
                    String firstChar = value.substring(0, 1);
                    if (firstChar.equals("["))
                        value = value.substring(1, value.length() - 1);
                    value = value.replace(", ", "','");
                    // 添加where  InCondtion 子句
                    sq.getWhereClause().addCustomCondition(new InCondition(new CustomSql(key), value));
                }
            } catch (Exception e) {
                logger.info("createSql, 发生错误,错误原因: ", e.getMessage());
            }
        }
        return sq.validate() + " order by id desc limit " + query_limit + " offset " + query_offset;
    }


}
